SOLVED

Dependent Drop Down in Template

Copper Contributor

I am using the 'Home Inventory' template to organize an upcoming move. I would like to add a dependent drop-down list in a column following the Room/Area column that is located in C10. For the dependent drop-down list, I would to be able to select a room in C then have the dependent drop-down list in D give the options of specific box numbers. For example, if Basement is selected as the room, the corresponding options would be 101,102, 103, .... and if Bedroom 1 is selected, then the corresponding options would be 201, 202, 203, ....2022-07-29.png

2 Replies
best response confirmed by Cre4ture (Copper Contributor)
Solution

@Cre4ture If you are using Excel 365 or 2021, please see attached.

 

You need to re-structure the Room Lookup. List all boxes and add the room to each box. Then use UNIQUE and FILTER to create dynamic lists that will feed the drop-downs for both the rooms and the boxes.

 

The Data Validation formula for column D uses INDEX and MATCH to find the correct list from column K.

This is exactly what I needed! Thank you!! A true scholar!
1 best response

Accepted Solutions
best response confirmed by Cre4ture (Copper Contributor)
Solution

@Cre4ture If you are using Excel 365 or 2021, please see attached.

 

You need to re-structure the Room Lookup. List all boxes and add the room to each box. Then use UNIQUE and FILTER to create dynamic lists that will feed the drop-downs for both the rooms and the boxes.

 

The Data Validation formula for column D uses INDEX and MATCH to find the correct list from column K.

View solution in original post