INDIRECT AND IF STATEMENT

[Guest]

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!

 

[Peo Sjoblom]

What's the point of doing this? Why can't you just use

=IF(I9=0,"","Special")

What do you have in C4?


--


Regards,


Peo Sjoblom

 

[Guest]

C4 is a list of named ranges from other worksheets within the workbook.
Depending on which range is selected by the user of the spreadsheet, I want
it to go to that range, see if the value of I9 is zero and return my yes or
no results.

 

[Peo Sjoblom]

Do you have more than one named range in C4. I will assume that you don't
because that would be foolish and that you probably have a validation in C4?
However I fail to see what it has anything to do with I9 if the name in C4
is a named range, do you always look what's in I9 in all those named ranges
if so it would be better if you had the different sheet names in C4. If it
is always the ninth cell in the named range that you want to test you could
use

=IF(INDEX(INDIRECT(C4),9)=0,"","Special")


or if you had the sheet names in C4


=IF(INDIRECT("'"&C4&"'!I9")=0,"","Special")



--


Regards,


Peo Sjoblom

 

[Guest]

yes, I am always looking at I9 in all of those ranges for this formula, but I
cannot use the sheetnames, because I am using the names of the ranges for
other dependent cell formulas for which I need the names of the ranges. So
is there a way to write the formula to reference a specific cell within a
range? In looking at your index formula, I would rewrite it to read
=(indirect(c4),l9 = 0, "", "special")
Does that make sense? But I am still getting an error.

 

[Peo Sjoblom]

Are the named ranges of equal size and if they are in what order is I9? If
they are the same size starting in the same cell but in different sheets
just give us the range and I will give a formula that will work. Meaning
that if the named range for instance is always I1:I15 then you can use the
INDEX formula I gave you. Or if it is A2: K50 then it could be done as well.
There is no way of using it the way you are trying to do but if they are of
equal size then yes it is possible


--


Regards,


Peo Sjoblom

 

[Guest]

I assumed that since the indirect reference works with my Vlookup formulas
going to those ranges on different sheets that it would work with an IF
statement.

Yes, my named ranges are all the same size. The size on each sheet is
A1:K357 and the cell is always I9, so a formula for that would be
appreciated. Thank you for all of your time. Pam

 

[Peo Sjoblom]

This should work then

=IF(INDEX(INDIRECT(C4),9,9)=0,"","Special")


--


Regards,


Peo Sjoblom

 

[Guest]

Peo, that worked perfectly. thank you so much for your help. Pam