+ Reply to Thread
Results 1 to 5 of 5

Display a dropdown list or populate a cell with single value based on other cells' values

  1. #1
    Registered User
    Join Date
    09-11-2014
    Location
    Portland, Oregon
    MS-Off Ver
    2010
    Posts
    10

    Display a dropdown list or populate a cell with single value based on other cells' values

    I'm using data validation and in cell CG4, what I would like to happen is:

    1 - If cell AF4 is "Individuals", then the user cannot do anything in cell CG4.
    2 - If Cell AX4 is "US" then populate cell CG4 with "United States".
    3 - If cell AX4 has a non-null value that is not "US", then cell CG4 should display a dropdown list where a country can be chosen.

    I've tried numerous things, but to show you my base starting point, here's the data validation formula i'm using that doesn't work:

    =IF(AF4="Individuals","",IF(AX4="US","United States",Sheet2!$F$2:$F$234))

    Sheet 2 contains the list of countries for the third scenario.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Display a dropdown list or populate a cell with single value based on other cells' val

    1) Put "United States" in Sheet2!$F$2
    2) Click on F2, then "name" that cell by typing US into the name box just to the left of the formula bar. Now that cell can be referred to by that name.
    3) Highlight F3:F235 (the regular country list) and name that entire range Countries the same way you did above.

    NOTE: You can also create named ranged manually by pressing CTRL-F3 to open the Name Manager.

    4) The Data Validation settings would then be:

    Allow: List
    Source: =IF(AX4="US", US, IF(AND(AX4<>"Individuals",LEN(AX4)>0), Countries))

    Notice the range of countries are both referred to by their names in that formula?
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    09-11-2014
    Location
    Portland, Oregon
    MS-Off Ver
    2010
    Posts
    10

    Re: Display a dropdown list or populate a cell with single value based on other cells' val

    Thank you for your help. Since posting, I began using this formula, which seems to accomplish the same thing:

    =IF(AF4="Individuals","",IF(AX4="US",Sheet2!$F$223,Sheet2!$F$2:$F$234))

    My issue is if AX4 is "US", I want CG4 to automatically be populated with "United States" so the user doesn't have to click the dropdown list to manually select United States. Your way and mine results in United States being the only option when AX4 is US, but I want that automatically displayed.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Display a dropdown list or populate a cell with single value based on other cells' val

    You can put a formula in CG4 that does that. THEN apply the data validation. That way if the formula gets a chance to display "United States", it will. But if they ever use the drop down on that cell, the formula would be forever deleted. But it's a good starting setup, one I've used on many of my own forms.

  5. #5
    Registered User
    Join Date
    09-11-2014
    Location
    Portland, Oregon
    MS-Off Ver
    2010
    Posts
    10

    Re: Display a dropdown list or populate a cell with single value based on other cells' val

    Thanks, Jerry. That makes sense and since I don't think folks will change the value in colum AX4, this should work nicely. Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Changing other cell values based on Dropdown list
    By FransB in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-25-2014, 04:00 AM
  2. Populate a range of cells based on a single cell with a variable value
    By RichardMichael in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-27-2014, 08:30 AM
  3. Populate a list based on a single cell
    By poppet in forum Excel General
    Replies: 7
    Last Post: 04-09-2014, 01:39 AM
  4. Replies: 6
    Last Post: 05-31-2013, 11:17 AM
  5. Auto-populate cell based on values in other cells
    By cyndimei in forum Excel General
    Replies: 8
    Last Post: 10-03-2008, 03:12 PM

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