+ Reply to Thread
Results 1 to 10 of 10

Drop Down Menu That Returns a Specific Value

  1. #1
    Registered User
    Join Date
    04-05-2013
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2010
    Posts
    4

    Drop Down Menu That Returns a Specific Value

    I have a data validation table that contains multiple cities. When a city is selected from the drop down menu, I want a value to be placed in a cell next to it.

    How can I do this?
    Population.png

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Drop Down Menu That Returns a Specific Value

    See this...

    http://contextures.com/xlFunctions02.html
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Drop Down Menu That Returns a Specific Value

    Hi shawner

    Have a look here, with reference to data validation with a dependant list.

    http://www.myonlinetraininghub.com/e...ependent-lists
    Regards Kevin


    Merged Cells (They are the work of the devil!!!)

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Drop Down Menu That Returns a Specific Value

    hi shawner, welcome to the forum. you can use VLOOKUP. assuming "Atlanta" in C4 & the list of Cities & Population in F3:G100, try this in D4:
    =VLOOKUP(C4,$F$3:$G$100,2,0)

    this is to find C4 ("Atlanta") in F3:F100 & when it finds it, return the 2nd column next to it. next time, Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,097

    Re: Drop Down Menu That Returns a Specific Value

    =if(A1<>"",VLOOKUP(A1,C2:D10,2,FALSE),"")

    I can not tell the ranges from the picture. So adjust A1 to cell with DV. C2:D10 to table of states/population, excluding header.
    Cheers
    Andy
    www.andypope.info

  6. #6
    Registered User
    Join Date
    04-05-2013
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Drop Down Menu That Returns a Specific Value

    Thank you so much, everyone. You guys are freakin' awesome!

    However, I am unsuccessful in my task. Here's what I've tried:

    I input the formula =VLOOKUP(B2,Pricing!A2:B27,2,FALSE) and I receive the error: "A user has restricted values that can be entered into this cell." What am I doing wrong?

    Pricing Tool.xlsxPopulation Tab.jpgPricing Tab.jpg

  7. #7
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Drop Down Menu That Returns a Specific Value

    shawner

    Can you not get shot of the data validation in C2 and use:
    =INDEX(Population!B2:B27,MATCH(B2,Population!A2:A27,0))

  8. #8
    Registered User
    Join Date
    04-05-2013
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Drop Down Menu That Returns a Specific Value

    Thank you, Kevin - I copy-and-pasted your formula and it works. I will take time to learn more about 'INDEX' and 'MATCH' functions.

    Regards,
    Shawner.

  9. #9
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Drop Down Menu That Returns a Specific Value

    No problem shawne, glad to of helped. See the link below for more on the index & match.

    INDEX & MATCH

  10. #10
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Drop Down Menu That Returns a Specific Value

    Clear the data validation rule for cell C2.

    Select cell C2
    Goto the Data tab
    Data Validation
    Click the Clear All button
    OK out

    Then, enter this formula in C2:

    =VLOOKUP(B2,Population!A2:B27,2,0)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1