How to Create Dynamically Changing Drop-Down Selection Lists

My question is about sheet 3 in the file attached.



In the column, A (for example A4) when has a value then in column E / Article Number (eg E4) has a dropdown for selection matching to column A. and to do that I have used the formula
=XLOOKUP(A7;Sheet1!$B$2:$BF$2;Sheet1!$B$3:$B$20;;0;1)



Now what I want to achieve is, in column A, say in cell A4 if I change the value then matching to that a new set of dropdown should appear in cell E4 matching the changed value in cell A4 for example. The Variant number should be populated from Sheet1!$B$2:$BF$2 and the drop-down should be matched as per sheet 1.



In short, if the user changes the Variant number in sheet 3 column A, the Article Number column E should also automatically change and give a new set of dropdown option matching the changed value of column A. Reference of values are in sheet 1.



Hopefully, I am not confusing.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. I will do it for you this time, as you are new.)

This recent and ongoing thread may give some insight: https://www.excelforum.com/excel-gen...drop-down.html