Trigger Dependent Drop Down List from Match Formula?

Question

I am trying to create a series of dependent drop down lists where each successive list is triggered based on the previous selection. This has been done many times, but the only way that I see to make this work is by using a static named range that matches the previous selection. My problem comes in that I need those values to be able to be customized by the end user.

The first drop down list is dynamic, and the user can change, add or remove options from it on another sheet. So I cannot rely on predetermined names ranges.

My thought was that I could name my ranges something based on the column location, and then use MATCH() to lookup the column number based on the custom entry from the first list.

The INDIRECT() function that is used in the Data Validation reference for the dependent lists does not work with formula driven cell values, so I cannot pull a range in based on anything but an already predetermined selection.

The lists that I need to reference are all dynamic, and the header for each list is also dynamic.

Perhaps there is another way to accomplish what I want without dependent lists.

Answer 1

You could use dynamic ranges with index/match like I suggested with a similar question here.

It would involve using sorted data, using match to return the first cell reference. Then, using the 2nd index to get the size of the range. example -

=Index($B:$B,Match([Value],$A:$A,0)):Index($B:$B,Match([Value],$A:$A,0)+CountA($A:$A,[Value]-1))

Where [Value] determines which list range to return, A:A is the possible selections for [Value] and B:B has the dependent lists.