Auto update data validation list in excel
Ngày đăng:
26/12/2021
Trả lời:
0
Lượt xem:
180
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 |