Auto update data validation list in excel

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

Video liên quan