Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
=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? |
#6
|
|||
|
|||
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? |
#7
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a dynamic list | Excel Worksheet Functions | |||
In Excel, why sort function fails when applied to a list of date? | Excel Discussion (Misc queries) | |||
Extracting Values on one list and not another | Excel Discussion (Misc queries) | |||
Does anyone have a copy of the Peter Noneley: Function list? | New Users to Excel | |||
Creating a list from an existing list. | Excel Worksheet Functions |