Excel - Dynamic reference based on the row I paste into?

Posted by michaelmichael on Super User See other posts from Super User or by michaelmichael
Published on 2011-01-17T21:31:33Z Indexed on 2011/01/17 21:55 UTC
Read the original article Hit count: 176

Filed under:
|

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, "")

© Super User or respective owner

Related posts about excel

Related posts about spreadsheet