Can I use a List in a IF Function?

[Guest]

I want to be able to have a IF function that says =IF(B77=Tile,Grout,"N/A") .

Tile - being a choice from a drop down list.
Grout - being a list of colors if the choose Tile.
"N/A" - if they do not choose Tile from the list.

Is this possible?

 

[Don Guillett]

Have a look in HELP index for

OR

 

[Jack Sheet]

Sounds like you are trying to create dynamic drop down lists.
Have a look at
http://www.xldynamic.com/source/xld.Dropdowns.html

Can be done without VBA, but a bit fiddly and not sure if it is more
efficient. That is of course always assuming that I have read the question
correctly.
Perhaps what you are trying is altogether simpler.

 

[Arvi Laanemets]

Hi

I'm not sure about "N/A" - is it Excel's returned error, or simply an user
entry. But in general, the next formula will do what you do want
=IF(SUMPRODUCT(--(B77={"Tile";"Grout";"Whatever"}))>0;"Is in list";"Is
missing from list")


Arvi Laanemets

 

[KL]

=IF(SUMPRODUCT(--(A4={"Tile";"Grout";"N/A"})),"Present","Not Present")

this formula will not report the error #N/A! as present.

KL

 

[Arvi Laanemets]

Hi again

It looks like I stumbled on some Excel odditie here. After I sent my answer,
I noticed that probably the formula I provided may be simplified. I tried
=IF(SUMPRODUCT((B77={"Tile","Grout","Whatever"})),"Is in list","Is missing
from list")
and discovered that it wasn't working - the check was always FALSE ?
BUT, then I tried
=IF(SUMPRODUCT(--(B77={"Tile","Grout","Whatever"})),"Is in list","Is missing
from list")
and the formula worked !!!???

PS. In my previous answer, probably function delimiters must be changed
(commas instead semicolons).


Arvi Laanemets

 

[KL]

Arvi,

It is not an odditie. The thing is that B77={"Tile","Grout","Whatever"}
returns TRUE/FALSE which can't be digested by SUMPRODUCT unless it is
explicitly coerced to 1/0, which is why u are using "--"

Regards,
KL