# Any way to duplicate a formula that needs individual relatively absolute references?

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"?

• 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. 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/… 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

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