I have a cell on worksheet A that includes a data validation list in cell c4.
The data validation list is a list of named ranges from other
worksheets--one range to one sheet.

I want to write a formula referencing the range name in cell C4 to go to a
cell within that range and if that cell = 0 (it has a formula in it), to
return nothing and if it doesn't, to return text.

I used: =if(INDIRECT(C4,I9)=0,"","SPECIAL") where I9 is the cell on the
worksheet containing the range named in C4.

I am getting a #value error. I traced the error and the dependent arrows
are pointing to cell I9 on the worksheet I am putting the formula into. What
am I doing wrong or is there a better way to accomplish this. Thanks!