Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Dropdown lists: Concatenate values to identify range?

Copper Contributor

Hey everyone,


I'd like to create a dropdown list that depends on 2 previous entries dropdown lists. I'll use an example of menu options to illustrate my problem!


The first dropdown is binary: either "meat" or "vegetarian". The second dropdown has multiple entries: "salad", "soup", "pasta", "baguette" etc etc. Combining entries from these 2 dropdowns would then define the possible entries for the third, which lists the various dishes available. For example, if I selected "meat" and then "pasta", the third dropdown would list the meat pasta dishes available. I'd like this to be a table, so:


Screenshot 2023-11-28 182301.png


This video using the OFFSET formula doesn't quite work as the second dropdown only depends on a single value from the first. I saw one Youtube video which shows the use of INDIRECT and concatenation which could perhaps solve it - the data validation from the third cell could combine the values of the first 2 cells to point to a specific range?


Any help would be much appreciated!

1 Reply



I'm attaching a demonstration of what I've called a "cascading" data validation (or drop down). The secondary and tertiary drop downs change based on prior selections. See if that (or something derived from it) would meet your need.