Using the IF function to do calculations based on the choices in drop down box

Cadeyz

New member
Joined
Jul 13, 2011
Messages
3
Reaction score
0
Points
0
Hi Guys,

What I'm hoping to achieve is the following:

I have a drop-down box that I created via Data Verification method and it has 5-6 choices in this drop-down box. When one choice is chosen and selected - other fields of the spreadsheet will automatically be filled out based on the choice chosen.

For example:

The drop-down menu may have the following:

Sian
Anna

If 'Sian' is chosen - the other fields will automatically be updated and so I felt the formula that would be in these 'other' fields that will auto update would have a similar function to the following: -

=IF(A1=Sian, 1800 456 432, Error) - bascially saying if Sian is selected, the particular cell that has this formula in it would return a phone number, or else it would return a value of 'Error'.

But it's not returning anything, just saying VALUE!

As I'm writing this - I'm guessing I'm going to need to have all the possible entries within the formula? ie: =IF(A1=Sian, Correct, (OR(A1=Anna, Correct2, Error)))

Even then I'm getting an error.

Is there any way to solve this problem?
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hi Cadeyz, and welcome to the forum!

Absolutely this can be done. I'm not sure how you created your data validation list (just typed the values in the data validation area maybe?) Ideally you should set up a table with the names of the people and their phone number. Then you can use a vlookup formula to pull back the appropriate phone number from your list.

I've attached a workbook that shows this setup.

Hope it helps, and if you have any questions, feel free to ask. :)
 

Attachments

  • xlgf271-1.xls
    26 KB · Views: 2,357

Cadeyz

New member
Joined
Jul 13, 2011
Messages
3
Reaction score
0
Points
0
Actually Ken - I just opened your attached file and have seen that you took my request to another level via VLOOKUP which I've heard about but never understood.
Now I'm reverse engineering your attached excel spreadsheet and all is good.

Your da man :)

Thanks again!
 
Top