ms excel - data validation for 2 drop down list

Question

I'm looking for the solution in excel data validation for 2 drop down list. When I choose 1st drop down list 2nd drop down list will auto populate the correct value from table. If user choose 2nd drop down list 1st drop down list will auto populate the value also. Can someone help me on this issue ?

I attach the sample file for my problem.

enter image description here

thanks!

Sample File

Did you look on Google? Plenty of examples of cascading data validation there... — jeffreyweir
I agree with @jeffreyweir. Please do some research, then take the tour and learn How to Ask a question here. — teylyn
yes. I had search from google, but those example only work for 1 way... change 1st drop down list populate value to 2nd drop down list. But what i need 2 way. change 2nd drop down list will populate value to 1st drop down list. maybe my knowledge was limited so need someone help to figure out how to do it. Thanks ! — dennis au

QHarr QHarr · Accepted Answer · 2018-04-19T07:03:49

Trying to do a two way I don't think is going to work in the way you are trying so I am not surprised the examples you found were for one-way. I am open to being corrected.

You could hack around it for example using two form control listboxes linked to the same cell so a selection in one updates the other.

Then because an item might be out of view listbox underneath have two cells which use the linked cell to index back into the source lists.

In the example above, there are two list boxes from form controls in developer tab (customize ribbon > add developer tab.

Developer tab form control - 2nd from the right

You add two of these in to the sheet.

Right click format control on each one

Set the input range to the range containing your list of values for that listbox and set the linked cell e.g. G1

Ensure that whilst you select different input ranges for each list box, they should have the same linked cell e.g. G1.

Underneath the listboxes put a formula which uses the linked cell G1 to index back into the source lists for each listbox so you can retrieve the selected value and have it visible, in case not visible within listbox.

Example testing:

ms excel - data validation for 2 drop down list

1 Answers