Forum Discussion
Excel Drop Down Question
Requesting assistance to set up an Excel drop down list with 2 entries/choices. Each choice returns a table 7 cells x 5 cells.
Looking for best solution using I believe xlookup and named ranges.
Please respond if willing to assist, tks
It's hard to understand what you're looking for, but I'm guessing a dynamic drop range? I've attached sample file I made from a different question that goes into conditional drop downs. This example has a drop down cell where the list of cities changes depending on the country selected.
Steps to create
- Create list of US cities from B1:B3 of Boston, Atlanta, Dallas
- Create German cities in C1:C2 of Munich, Berlin
- In cell A1 enter this formula: =if(G4="USA",B1:B3,C1:C2)
- Create a named range of listValidation that is =Sheet1!$A$1# (important to note the hashtag)
- For you data validation rule in G5 set the validation rule for List =indirect("listValidation")
If you're really clever it's do-able with XLOOKUP but probably not sensible. A simpler solution might involve FILTER or INDIRECT but it's difficult to go into more depth without a sample of the workbook.
Please provide detailed information.
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
