How do you make a set of cascading dropdown lists in excel?
I'm going to assume when you refer to the contextures method, you're talking about the INDIRECT method...
A better method for this sort of complex an multi-tier relationship is the OFFSET/MATCH method, detailed here: http://www.contextures.com/xlDataVal13.html
It only requires list management for each layer.
For layers beyond the second, I simply append the prior layer names together to create a unique path to the lower level.
Note, creating these lists is not as onerous as it seems. It can be automated by making pivot tables over a master list.
This is the way (I hope the translation works because of my regional version):
- Validation data
- Allowed by list ("Elenco" in italian version)
- Origin: here you have to insert your formula: =if(A1="Conifer";B1:B150;if(A1="Leaf";C1:C150;....)) where in B1:B150 you can find type of Conifer, C1:C150 type of Leaf an so on
