Results 1 to 5 of 5

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    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.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    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-vid...html#Dependent

    Thanks Ken for your input

    =Cadeyz=

  4. #4
    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!

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    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!
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •