Excel – Increase the Row referenced in INDIRECT when dragging a formula down

microsoft excelmicrosoft-excel-2010worksheet-function

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

etc.

As opposed to having alter the formula on each row.

=IF(SUM(INDIRECT("'"&A2&"'!b5")+INDIRECT("'"&A2&"'!C5")+INDIRECT("'"&A2&"'!D5"))>=1,"Yes","No")

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.

Cheers,

Danny

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

Best Answer

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.

Related Question