Excel - Dynamic reference based on the row I paste into?
- by michaelmichael
I have a simple, oft-used formula that I paste into spreadsheets I receive. It looks like this:
=IF(AND(D8="COMPLETE",E8=""),A8,"")
It looks in D8 for the word "COMPLETE" and checks that E8 is blank. If both conditions are fulfilled it grabs the contents of A8. It works fine.
The only problem with this is that I don't always paste it into row 8. Every spreadsheet is different. I usually end up spending a few seconds making the formula fit the current spreadsheet I'm working on by dragging the cell references to the appropriate row. Hence, my question: Is there a way to make an absolute row reference based on whatever row I paste into?
For example, if I paste the above formula into a cell in row 25, the formula would automatically look like this:
=IF(AND(D25="COMPLETE", E25=""), A25, "")