Can I use a List in a IF Function?
 

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?

Have a look in HELP index for

OR

--
Don Guillett
SalesAid Software

"Matt" wrote in message
...
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?

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.

--
Return email address is not as DEEP as it appears

"Matt" wrote in message
...
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?

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


"Matt" wrote in message
...
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?

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

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

KL

"Matt" wrote in message
...
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?

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


"Arvi Laanemets" wrote in message
...
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


"Matt" wrote in message
...
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?

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


"Arvi Laanemets" wrote in message
...
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


"Arvi Laanemets" wrote in message
...
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


"Matt" wrote in message
...
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?