If statement with indirect problems

Question

My sheet is set up with I1 as a drop box for other tabs. A1 and B1 Formula works and pulls the info from the cell in the tab if there's info, with slight changes in formula for cells but you can get an idea from this.

=IF(INDIRECT("'"&I1&"'!"&"A1"),INDIRECT("'"&I1&"'!"&"A1"),"")

My C1 is the one giving my problems.

=IF(INDIRECT("'"&I1&"'!"&"C2"),"Yes","No")

If there is no information in the referenced C-cell it places No. If there is any text there I receive the #Value! error. Both cells are set to general. I've been fighting with it for about an hour and I'm stuck. How can I solve this?

Answer 1

In the IF, you are not comparing your cell to anything. The first parameter to IF should be a logical term (true or false), and IF then takes the second or the third parameter accordingly.

INDIRECT("'"&I1&"'!"&"C2") will result in the content of that cell, but you didn't tell the IF what to do with it. So it tries to convert the content of that cell to true or false - if the cell is empty, that becomes false, and if the cell contains text, it can't convert it and gives #VALUE!.

If you don't care about the text itself and just want to check if it is there or not, compare to the empty string:

=IF(INDIRECT("'"&I1&"'!"&"C2")<>"","Yes","No")

Otherwise, compare to whatever you want to compare to.