1

I have an indirect function in excel which works like it should =INDIRECT(B15)

Now I would like to have it when the cell it is pulling data from is blank then the indirect cell is also blank.

I thought something likes would work but it does not. =IF(B15="",INDIRECT(B15))

Please advise

flyers
  • 514
  • 2
  • 5
  • 16

2 Answers2

0

You forgot the True criterion in the IF Function:

 =IF(B15="","",INDIRECT(B15))
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • 1
    So I plugged that into the cell via the data validation process and it came back with a statement that the list source must be a delimited list, or a reference to a single row or column – flyers Feb 24 '16 at 17:00
  • Then you should ask a new question, because that is not what you asked for. – Scott Craner Feb 24 '16 at 17:15
0

If B7 is blank (or cannot be resolved to a cell address) then the INDIRECT function is going to return a #REF! error. This is shorthand for Reference Error; it means that whatever is in B7 cannot be evaluated as a cell range address.

To show a zero-length string (aka look blank) then the IFERROR function can catch the error and return a zero-length string; e.g. "". If evaluating B7 as a cell address does not produce an error then the regular result is returned.

=IFERROR(INDIRECT(B7), "")
  • 1
    So I plugged that into the cell via the data validation process and it came back with a statement that the list source must be a delimited list, or a reference to a single row or column – flyers Feb 24 '16 at 16:55
  • Yes, sometimes bending the rules ends up breaking them. –  Feb 24 '16 at 16:57
  • lol it partially works if I don't use the data validation, but then my drop down selections do not appear. – flyers Feb 24 '16 at 17:01
  • There are better methods than the volatile INDIRECT to get this sort of thing done. An INDEX function with a MATCH on the column header labels to B7 would be one. But all that is moot and really has nothing to do with the question at hand (which incidentally never mentioned data validation). –  Feb 24 '16 at 17:05