suitable formula

[HVK1986]

I have data validated 3 feilds in A1.

if i select apple in a1, in b1 it shouls come good,if i select mango in b1 it should come "better", if i select banana in b1 it should come "best" like vise i have 5 conditions.....


Now if i dont select anything the cell b1 should be blank...


Pls provide a suitable formula

 

[bobhc]

Good day HARRY


Both your recent post would work with dependent data validation, a search in the top right box will bring up many post to help you. SHOUTING IN YOUR TITLE WILL NOT HELP, IT PUTS PEOPLE OFF. (PREVIOUS POST)

 

[kaushik03]

Hi HARRY,


I assume you have 5 elements: Apple, Mango, Banana, Orange, Pinepple.


Your conditions are as follows:


IF A1= Apple THEN B1= Good

IF A1= Mango THEN B1= Better

IF A1= Banana THEN B1= Best

IF A1= Orange THEN B1= Bad

IF A1= Pinepple THEN B1= Worse


To do the same plz follow the below instructions:


1)List all the elements (Apple, Mango, Banana, Orange, Pinepple) somewhere in excel sheet; say from I1:I5

2)Create the drop down list for these elements at a1( press ALT+A+V+V) from your key board (short cut for MS 2007) to bring data validation dialogue box. Select "List" from drop down box under "allow". Under source write =$I$1:$I$5 and hit OK.

3)Now at B1 write =IF(A1="Apple","Good",IF(A1="Mango","Better",IF(A1="Banana","Best",IF(A1="Orange","bad","worse")))) and press enter.


Now change elements from your drop down list( at A1 ), formula should automatically update for the conditions specified.


Hiope this helps.


Kaushik

 

[HVK1986]

@bobhc: dint get u, where did i shout in the previous post...

 

[HVK1986]

Thanks a lot kaushik...

 

[HVK1986]

@Kaushik

if nothing is selected, b1 should be empty...how to do it?

 

[shrivallabha]

Retaining Kaushik's layout and data validation except for following

[pre]

Column I  Column J
Apple	   Good
Mango	   Better
Banana	   Best
Orange	   Bad
Pineapple  Worse

[/pre]
Following formula can be used:

=IFERROR(VLOOKUP(A1,I1:J5,2,0),"")

 

[Trader273]

When you have list like you do and looking for 1 value, using IFs, vlookups, etc can get quite cumbersome. I have attached a simple function that uses the Select Case to create a simple UDF. If the selected fruit is empty, then will show "Please enter a fruit", but can be easily changed to just be blank. Hope it helps.


https://www.dropbox.com/s/d5syz5q4i2jr8oy/Select%20Fruit.xlsm

 

[kaushik03]

Hi Harry,


Unfortunately I missed your further post and sorry as I did not catch you other criteria/requirement(if nothing is selected)...


But, meanwhile, I could see that people have already replied to that (with formula and UDF approach).


Please advise in case of any issue.


Kaushik