0

I need to duplicate a formula down for many rows. However each one needs to be an absolute reference, referencing a specific cell in that individual row. I can't just fill down the original formula with an absolute reference, or every row will be identical.

Example formula:

=IF(AND(ISBLANK(D$5),ISBLANK(H$5)),"",IFERROR((SUM(D$5,H$5)), "ERR"))

Desired result after filling down one cell:

=IF(AND(ISBLANK(D$5),ISBLANK(H$5)),"",IFERROR((SUM(D$5,H$5)), "ERR"))
=IF(AND(ISBLANK(D$6),ISBLANK(H$6)),"",IFERROR((SUM(D$6,H$6)), "ERR"))

I need to do this, because I will be inserting (and shifting down by one) cells in columns A, D, and H, every day. Without the absolute references, the formulas above will change to follow where the data has moved, and I need them to always track whatever happens to be in those cells at the moment, but I have hundreds of rows to fill.

Is there any way to replicate a formula like this, hundreds of times down the sheet, while creating such "relative absolute references"?

4
  • 1
    You may use INDIRECT() formula to fix absolute reference - INDIRECT("D"&Support!A5)) (support sheet with ascending numbers with step 1 to get row reference), or other method to get fixed row number like COUNTA(range with fixed data) etc. – Oleg_S Sep 24 '20 at 7:45
  • Ah yes, Indirect! That was nagging at the back of my mind but I couldn't quite remember what it was... I'll give it a shot, thanks! – JVC Sep 24 '20 at 12:23
  • does this reference help? webapps.stackexchange.com/questions/95102/… – 24601 Sep 24 '20 at 12:52
  • Pretty similar to what I ultimately came up with myself. There is one "gotcha" though, I'm going to write my answer an explain it. – JVC Sep 24 '20 at 15:00
1

Here's what I ultimately came up with:

=SUM(indirect("D"&cell("row", I5)),indirect("H"&cell("row", I5)))

There is one catch that I missed for a bit but once I saw it, I realized what I was overlooking.

The cell you're going to use to get the cell("row") from, has to be in a row that is NOT going to move. It doesn't even have to be a cell in use, though I chose one that is. So in my case I used column I.

Now I can insert and shift down in columns D and H, and all my sums off to the right follow my changes. Works great!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.