# Increase the Row referenced in INDIRECT when dragging a formula down

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

• 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

``````=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")