Excel vba : Synchronize two drop down lists in the same worksheet

Question

I'm starting to use Excel VBA to create drop down lists using data validation but i would like to synchronize two drop down lists in the same worksheet.
I created the first list which is 1, 2, 3, 4, 5 and I created the second list which is A, B, C, D, E

My purpose is when I choose Item number 3 from the first list (which is 3) automatically the Item number 3 (which is C) from the second list will be displayed.

I tried to use the INDEX and MATCH fct but without success could you please help me with that

Thank you for your help Hichem.

Answer 1

If you have a data validation drop-down in a cell, you shouldn't have a formula in that cell at the same time. As a rule, a cell should either be calculated or used for data entry, not both.

Using a formula, you can put the values you want to calculate in the second cell into the spreadsheet somewhere, if they are not already. Then you can use a simple Vlookup or Index/Match. For example, all drop-down list options are stored on a sheet called Setup. The values for the second data validation list are in the cells C2 to C6. The values for the first data validation list are in cells A2 to A6. You can now use the value selected in the data validation list like this:

=index(Setup!$C$2:$C$6,match(Sheet1!C2,Setup!$A$2:$A$6,0))

In words: in column A of the setup sheet, find the value that is the same as C2 in Sheet1 and return the value from column C of the setup sheet in the same row.