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

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?

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.

Problem solved.

Yesterday I was researching this problem and came across some video tuts regarding this issue which as a video-tutorial fan, I loved it. The link is as follows:

http://www.contextures.com/excel-videos-data-validation.html#Dependent

Thanks Ken for your input

=Cadeyz=

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!

Glad to help.

Debra also has some VLOOKUP videos on her site you may want to check out. http://www.contextures.com/excel-videos-vlookup.html

Cheers!