Forum Discussion
Refresh Drop-down list
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
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
- GJoe_CIAug 12, 2020Copper ContributorIs there any way to refresh a drop down list with the first element of the list?
In this example X will remain, but is there any way to said excel pick the first element by default?- peteryac60Aug 13, 2020Iron Contributor
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:
- if you list have more that 3 values then you need to manually adjust the code
- the drop down list is in cell D2. If you need it to be in another cell, you need to adjust the code
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