+ Reply to Thread
Results 1 to 4 of 4

Cull dropdown list options based on selection in another dropdown

  1. #1
    Registered User
    Join Date
    08-05-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Cull dropdown list options based on selection in another dropdown

    I have two dropdown lists, one with names, and the other with holdings associated with those names. Both dropdown lists are populated by named ranges ("People" and "Items"), and the named ranges' references are:
    People: =OFFSET(Sheet1!$A$46,0,0,MATCH("*",Sheet1!$A$46:$A$1169,-1),1)
    Items: =OFFSET(Sheet1!$B$46,0,0,MATCH("*",Sheet1!$B$46:$B$1169,-1),1)

    The reference for the first dropdown list is simply =People. However, I'm unsure what to put as a reference for the second dropdown list that's based on "Items". I'd like for the list to be trimmed based on the person selected. Here's a couple example lists:

    Dropdown list 1:
    John
    Mike
    Carl

    Dropdown list 2:
    John's car
    John's phone
    Mike's boat
    Mike's dog
    Carl's teeth
    Carl's bottle

    For instance, if John is selected in dropdown 1, I'd like dropdown 2 to only list John's car and John's phone as options.

    Constraints:
    New people and items will be added to the listed regularly enough that creating lists for individual people and item groups is not sufficient.
    Dropdown 2 will always be directly to the right of dropdown1, and each dropdown list occur dozens of times in those columns, so that multiple people can be selected. How can I make it so that dropdown2 always refers to the cell on its left?

  2. #2
    Registered User
    Join Date
    11-14-2012
    Location
    Brooklyn, NY
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Cull dropdown list options based on selection in another dropdown

    the 2nd dropdown list should be populated by this function: =OFFSET(Sheet1!$A$46,MATCH(A1&"*",Sheet1!$A$46:$A$1169,0),0):OFFSET(Sheet1!$A$46,COUNTif(Sheet1!$A$46:$A$1169,A1&"*"),0)

    If it does not work please see attach a sheet so i can feel it in for you.

  3. #3
    Registered User
    Join Date
    08-05-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Cull dropdown list options based on selection in another dropdown

    Going to data validation and selecting list, and trying to enter that gives me the following error message:
    "You cannot use references to other worksheets or workbooks for data validation criteria." The only change of note that I've made is giving the sheet a name. I simply changed all instances of sheet1 into that name, but the error persists. Here's the sheet: https://rapidshare.com/files/2162642703/Combat.xlsm - You'll note that the sheet is basically split into two halves, with the right half being a mirror of the left. Dropdowns in column A select from the list that starts in A46, and dropdowns in column B should select from the list starting in B46. Dropdowns in AG and AF will draw from the same lists, as evident by AF already being setup to draw from A46 and down.

    It's probably worth pointing out that the next phase of this will be getting the option selected in dropdown list of column B to populate the fields in that row based on the stats in the unit library. Military mod and power will always simply be the listed value for that unit (blank values are fine, the function that totals that row automatically fills in blanks with a 0 or 1 as needed), but terrain is going to be tricky, because what it puts in the terrain field for that row will vary depending on the true/false values in N2, N3, and N4. If all 3 values in column N are marked true, I'd like it to use the average of the values in the air/earth/water column of a units' entry in the library. If only some are marked true, I would only like it to average among the values marked true. I'd also like it to always round up to the nearest whole number. I bring this up in case it's relevant to how the dropdown list would need to be written. I didn't figure it would be, and if that's the case I'll try it myself, and make a new thread for it if I can't figure it out on my own.
    Last edited by Kiffar; 11-16-2012 at 03:00 AM.

  4. #4
    Registered User
    Join Date
    11-14-2012
    Location
    Brooklyn, NY
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Cull dropdown list options based on selection in another dropdown

    Please see attached sheet, i've entered data validation in columnn 'B' & 'AF', please let me know if you need help with the next steps in other formulas

    Thank you
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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