Just wondering if anyone knows how to increase the row referenced in the INDIRECT when dragging a formula down a column.

To elaborate the formula is below, i'd like the cell referenced i.e. B5, C5 & D5 to increase the row referenced by +1 each time when i use drag to copy the formula e.g. so it'd go

B5, C5 & D5
B6, C6 & D6
B7, C7 & D7


As opposed to having alter the formula on each row.


I'm sure it's just something like the ROW function that'll do it but I can't seem to crack it.

Any help would be much appreciated and thanks in advance.



P.S. The INDIRECT is being used to reference a different sheet as determined by what is in cell A2

  • The A2 ref. should also be locked i.e. $A$2 forgot i'd took those out for a second before pasting – Danny Feb 6 '17 at 13:30

Pull the row number out of the quotes and use ROW():

=IF(SUM(INDIRECT("'"&$A$2&"'!B" & ROW(5:5))+INDIRECT("'"&$A$2&"'!C" & ROW(5:5))+INDIRECT("'"&$A$2&"'!D" & ROW(5:5)))>=1,"Yes","No")

Now it will iterate as the formula is dragged/copied down.

|improve this answer|||||

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.