3 Tiered Drop-Down Lists with Data Validation in Excel

Question

From the following my data HERE, I am trying to create a dropdown validation list. e.g. https://www.youtube.com/watch?time\_continue=146&v=og4lot5gYrs

But the named ranges and INDIRECT method as shown in the video doesn't work because I have duplicate data across the columns. Does anyone know how to do this in a different way, so that I am able to select the categories in Selection 1 which filters the the only possible options in Selection 2 and that in turn filters the only possible options in Selection 3 , don't mind if it has to be a VBA macro.

Answer 1

Hi Kunal,

Duplicate data in a drop-down list usually result in errors. You have to create a list for each category and use a unique Define Name. Once you’re done, you can proceed with creating a dependent drop-down list using Data Validation and the INDIRECT function.

Using the data in the sample file you’ve shared, I was able to create a dependent drop-down list. Here’s a link to the edited file stored in OneDrive Online.

Let me know if you need additional assistance.