Create Dependent Lists with Auto-Refresh/Update

[afares]

Hi All,

I want to create many dependent drop list (more than 100) that auto refresh when the source list changes.
I am able to auto update the list using data validation and by changing the source list using named ranges but the selected item doesn't change unless I re-select the data validation list.

lets take as a simplified example the following:

**category_List:**

colors = {black, red, green}
languages = {english, french, spanish}

So if i select colors, i want to display the colors list and if I select the languages then I want to select the languages list. If red was selected then when i change to languages category, I want the french to be automatically selected and so on.

I am aware of two solutions:


1)

Uses VBA to do that, but since I have more than 100 list and the dependent list are random then I have to manually change each list when a specific source list changed. I dont think this the best idea.


2)

A better approach as this will automatically change the selection when the category changes and it also give an additional feature which is the linked cell that gives a bi-directional connection between the cell and the combo box.
But my issue is that linked cell returns a 1-based index and I want that to be 0-based index. So if I select the first element, I want the linked cell to display 0 not 1 and vice versa...


Is there a way to achieve what I want easily ? either using one of the proposed approach or using another one ?

thanks

 

[Narstrabest1995]

It is not easy to do with bulk in a single drop down and it can be split so you care do before calculation

 

[afares]

It is not easy to do with bulk in a single drop down and it can be split so you care do before calculation

No I have more than 100 list and there are inter-dependencies between some of them. I am able to manage changing the source lists that are nothing but named ranges. The problem is how to reflect the changes to the droplist so that the selection is automatically re-selected when the source list changes.