user checklist formula needed XP

[Newbie Bob]

I have a task list of things that need to be done on RMA's.
what I want to do is have a cell where the user inputs a model number or
description (perhaps 6 choices), then the cells that don't apply to that part
number would display an N/A

=IF(SEARCH("SS", C6, 1)>0, "N/A", " ") works only for words containing ss,
anything else creates an error.

=IF(C6="SS", "N/A", " ") works only if the input is ss

Any help would be greatly appreciated.

Thanks.

 

[Zack Barresse]

Well, you could always nest AND statements ...

=IF(AND(ISERR(FIND("a",A1,1)),ISERR(FIND("b",A1,1)),ISERR(FIND("c",A1,1)),ISERR(FIND("d",A1,1)),ISERR(FIND("e",A1,1)),ISERR(FIND("f",A1,1))),"Not
Found","OK")

HTH

 

[Bob Phillips]

or a SUMPRODUCT

=IF(SUMPRODUCT(--(ISNUMBER(FIND({"a","b","c","d","e","f"},C6))))>0, "N/A", "
")

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Newbie Bob]

woohoo!!!!

Yes!! That was what i needed.

Here is what i ended up with;

=IF(AND(ISERR(FIND("SS",C6,1)), ISERR(FIND("RL",C6,1)),
ISERR(FIND("DMD",C6,1)), ISERR(FIND("DMM",C6,1)), ISERR(FIND("DMS",C6,1)),
ISERR(FIND("DRS",C6,1))), "", "N/A")

Thanks a lot!

 

[Newbie Bob]

Since my user input was text, this formula didn't work for me, but thanks for
the efforts!

 

[Bob Phillips]

It is meant to, and does work, on text. Using your data in later post, it is

=IF(SUMPRODUCT(--(ISNUMBER(FIND({"SS","RL","DMD","DMM","DMS","DRS"},C6))))>0
, "N/A", " ")

which is far more maintainable than the nested IFs.

--

HTH

RP
(remove nothere from the email address if mailing direct)