checking cell 'tesxt' value

[mwvirk]

i'm using drop down menu in cell a1 where list of some items is created like:
Orange
Apple
Banana
Onion
Carrot
Grapes

in cell b1 i want to check if a1 is Onion or Carrot then it should give value in b1 "Veg" and if cell a1 is Orange or Apple or Banana or Grapes then b1 should have value "Fruit"

please help me to correct my formula:
=IF(ISNUMBER(FIND("Orange","Apple","Banana","Onion","Carrot","Grapes"",A1)),"Veg","Fruit")

 

[T. Valko]

according to below formula, Receipt Printer should be "Partial" but its giving me output as "Full"

=lookup(i4,{"BNA","Card Reader","Cash Handler","Dispenser","Receipt Printer","Journal Printer","Capturing Cards","Out Of Cash","Low On Cash","No Transactions","Wrong value","Negative Balance","Pick Failure","Communication","Display","Hardware","Software","DV-RW","Camera"},{"Partial","Full","Full","Partial","Partial","Full","Full","Full","Partial","Full","Partial","Partial","Par tial","Full","Full","Full","Full","Partial","Partial"})

IMHO, using formulas hardcoded with that many variables is a bad habit to get into.

You should use lookup tables.

 

[mwvirk]

Maybe this...

=IF(AND(M4<>"",U4<>""),"Resolved",IF(M4<>"","Pending",""))

yes, it is like this and i should be like this.
thnx a lot for solving my 1 problem. see if u can help me in =LOOKUP issue.
there is one reason, i cannot use to new tabs as you said earlier. bcoz when i copy and insert 1 row (as it is required in my sheet) then it change the cell range. and formula goes wrong.
thnx

 

[T. Valko]

yes, it is like this and i should be like this.
thnx a lot for solving my 1 problem. see if u can help me in =LOOKUP issue.
there is one reason, i cannot use to new tabs as you said earlier. bcoz when i copy and insert 1 row (as it is required in my sheet) then it change the cell range. and formula goes wrong.
thnx

Use absolute references when referring to the lookup table or give the lookup table a defined name.

Trust me, you'll be better off using lookup tables.

 

[mwvirk]

thnx MrKowz & #Name?
both of u solved my 2 issues.

 

[mwvirk]

just to add few more options:
dropdown menu is there in i4 with several items and based on that full/partial is assigned in k4
we defined "Software" as a full outage but it is possible that "Software" might be a partial outage.
i'm giving option in the same cell to add 1 more dropdown menu only to select full/partial
but after adding the dropdown menu, its removing my formula.
is it possible to keep the formula as it is. and give the option for dropdown menu also. in this way, automated selection is possible. but for some reasons if user is not satisfied then giving him option to change the outage field manually.
help pls...

 

[mwvirk]

another issue:

<TABLE style="WIDTH: 152pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=202 border=0><COLGROUP><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" width=50><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><TBODY><TR style="HEIGHT: 39.75pt; mso-height-source: userset" height=53><TD class=xl69 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 79pt; BORDER-BOTTOM: black 1.5pt solid; HEIGHT: 62.25pt; BACKGROUND-COLOR: #d8d8d8" width=105 height=83 rowSpan=2>Problem Description</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 38pt; BORDER-BOTTOM: black 1.5pt solid; BACKGROUND-COLOR: #d8d8d8" width=50 rowSpan=2>Remedy Ticekt Number</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 35pt; BORDER-BOTTOM: black 1.5pt solid; BACKGROUND-COLOR: #d8d8d8" width=47 rowSpan=2>Outage</TD></TR><TR style="HEIGHT: 22.5pt; mso-height-source: userset" height=30></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>BNA</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Partial</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR></TBODY></TABLE>

when cell is having value, its giving me proper info. but if "problem description" is empty or say no problem is reported then its giving formula error #N/A

 

[mwvirk]

i have solved my problem myself

=IF(ISERROR(LOOKUP(I4,{"Bna","Camera","Capturing cards","Card Reader","Cash Handler","Communication","Dispenser","Display","DV-RW","hardware","Journal Printer","low on Cash","Negative Balance","No Transactions","Out of Cash","Pick Failure","Receipt Printer","Software","Wrong Value"},{"Partial","Partial","Full","Full","Full","Full","Partial","Full","Partial","Full","Full","Partial","Partial","F ull","Full","Partial","Partial","Full","Partial"})),"",LOOKUP(I4,{"Bna","Camera","Capturing cards","Card Reader","Cash Handler","Communication","Dispenser","Display","DV-RW","hardware","Journal Printer","low on Cash","Negative Balance","No Transactions","Out of Cash","Pick Failure","Receipt Printer","Software","Wrong Value"},{"Partial","Partial","Full","Full","Full","Full","Partial","Full","Partial","Full","Full","Partial","Partial","F ull","Full","Partial","Partial","Full","Partial"}))

 

[MrKowz]

A possible shorter solution:

=IF(I4="","",(LOOKUP(I4,{"Bna","Camera","Capturing cards","Card Reader","Cash Handler","Communication","Dispenser","Display","DV-RW","hardware","Journal Printer","low on Cash","Negative Balance","No Transactions","Out of Cash","Pick Failure","Receipt Printer","Software","Wrong Value"},{"Partial","Partial","Full","Full","Full","Full","Partial","Full","Partial","Full","Full","Partial","Partial","F ull","Full","Partial","Partial","Full","Partial"}))

However that would still error for any value less than "Bna"... ie, if you typed "a", "b", "ba", "bm", "bmz", etc... it would still error

(Sorry for not getting back to the post earlier, left work and had a lot to do tonight.)

 

[mwvirk]

A possible shorter solution:

However that would still error for any value less than "Bna"... ie, if you typed "a", "b", "ba", "bm", "bmz", etc... it would still error

(Sorry for not getting back to the post earlier, left work and had a lot to do tonight.)

how it can give error when no one can type "a" "b""bma" etc bcoz i'm drop down menu, i'm setting up for only BNA Card Reader etc.

only problem is, how to convert the word in proper case. like if user type card reader then it should appear (after the entry) Card Reader.
another issue is BNA. if we setup a formula (i don't know how) then if user type "bna" then formula can convert it to only "Bna" where we need "BNA"

help help help...