Using indirect function in an IF statement with quotes

0
votes

I'm making multiple IF statements that are going to have the same layout. Instead of writing the reference sheet name I'd like to reference a cell for the sheet name.

Also in the interests of laziness I'd like to drag the formula so it changes locations it is looking at on the referenced sheet.

At the moment it looks like this.

=IF(sheet1!O2="","",sheet1!O2)

Simple enough.

However I want to use indirect and I can't write it without getting an error.

Last attempt was

=IF((indirect($B$3))!O2="","",(indirect($B$3))!O2)

where Sheet1 is in the cell B3

Doesn't work.

Any help on the correct syntax would be very appreciated.

1
excelif-statementdouble-quotesexcel-indirect

1 Answers

2
votes

You need to concatenate $B$3 and "!O2" to generate "Sheet1!O2" as a string for INDIRECT to work, as below:

=IF(indirect($B$3&"!O2")="","",indirect($B$3&"!O2")