Jun 15 2020 01:06 AM
Good afternoon, everyone,
I have a question about updating various drop-down lists.
In Excel, I have created a "master list" where the different raw data for the dropdown are stored.
Now I have selected the different data for the dropdown.
Now my actual question:
Is there a simple and elegant way to change the data from the master list afterwards and the previously selected dropdown is automatically updated to reflect the change.
I would be very happy about a possible solution.
Greeting
Jun 15 2020 03:39 AM
Hi
I'm afraid it is not very clear what you want? Can you attach a sample?
Do you have 2 lists - a master and an other that you want to somehow switch between them. Or do you have several lists held on a 'master' sheet and you want to switch between those? if that is the case what is the test that needs to apply to change from say List 1 to List 2 or List 3 etc.
If you can attach an example that often makes it easier to understand.
thanks
Peter
Jun 17 2020 01:50 AM
Hello, Peter,
I have a master list. All important data is stored in master list.
In the other Excel-Sheets A and B there are dropdowns that draw their data from the master list.
I now select the data for Excel sheets A and B using the drop-down menu.
The data was transferred into the cell.
Suppose I want to change the data from the cells on Excel sheets A and B now.
For this I go to the master list and change the data from the master list.
Now I want the data in the cells on Excel sheets A and B to be changed automatically.
Until now, the old data in the cells on Excel sheets A and B remain. To achieve the change, however, you have to select the changed data again using the drop-down menu.
Is there a solution for my wish?
warm greetings
Jun 17 2020 02:17 AM
Hi Lukas,
In the attached the 'master' sheet has a list X, Y, Z.
In the main sheet you select from the drop down the value you want - as an example X.
If I understand your issue you want to change the drop down from X,Y,Z to A,B,C?
You ,of course, can do this. On the main sheet the X will remain until you select one of A,B or C.
Excel has no way of changing X because there is no way of knowing if you want A,B or C until you select.
Sorry!
Peter
Aug 12 2020 11:35 AM
Aug 13 2020 03:32 AM
Hi
The attached file uses some VBA code so that if the drop down values changes from (say) X Y Z to A B C on the Master sheet then the default value will be A i.e. the first value on the new list.
However - there are limitations. Examples are:
The code is very simple so adjusting it should not be difficult.
You will need the developer tab to access the code - if you don't have this already you can access from FILE->OPTIONS->CUSTOMIZE RIBBON-> on the right hand pane ensure the DEVELOPER option is checked.
hope this helps.
Peter