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?
Bookmarks