Refresh Drop-down list

%3CLINGO-SUB%20id%3D%22lingo-sub-1462763%22%20slang%3D%22de-DE%22%3ERefresh%20Drop-down%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1462763%22%20slang%3D%22de-DE%22%3E%3CP%3EGood%20afternoon%2C%20everyone%2C%20%3CBR%20%2F%3E%20I%20have%20a%20question%20about%20updating%20various%20drop-down%20lists.%20%3CBR%20%2F%3E%20In%20Excel%2C%20I%20have%20created%20a%20%22master%20list%22%20where%20the%20different%20raw%20data%20for%20the%20dropdown%20are%20stored.%3C%2FP%3E%3CP%3ENow%20I%20have%20selected%20the%20different%20data%20for%20the%20dropdown.%20%3CBR%20%2F%3E%20Now%20my%20actual%20question%3A%20%3CBR%20%2F%3E%20Is%20there%20a%20simple%20and%20elegant%20way%20to%20change%20the%20data%20from%20the%20master%20list%20afterwards%20and%20the%20previously%20selected%20dropdown%20is%20automatically%20updated%20to%20reflect%20the%20change.%3C%2FP%3E%3CP%3EI%20would%20be%20very%20happy%20about%20a%20possible%20solution.%3C%2FP%3E%3CP%3EGreeting%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1462763%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1463017%22%20slang%3D%22en-US%22%3ERe%3A%20Refresh%20Drop-down%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1463017%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F699325%22%20target%3D%22_blank%22%3E%40Lukas_Feix_HAWE%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20afraid%20it%20is%20not%20very%20clear%20what%20you%20want%3F%20Can%20you%20attach%20a%20sample%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20have%202%20lists%20-%20a%20master%20and%20an%20other%20that%20you%20want%20to%20somehow%20switch%20between%20them.%20Or%20do%20you%20have%20several%20lists%20held%20on%20a%20'master'%20sheet%20and%20you%20want%20to%20switch%20between%20those%3F%20if%20that%20is%20the%20case%20what%20is%20the%20test%20that%20needs%20to%20apply%20to%20change%20from%20say%20List%201%20to%20List%202%20or%20List%203%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20can%20attach%20an%20example%20that%20often%20makes%20it%20easier%20to%20understand.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1470034%22%20slang%3D%22de-DE%22%3ERe%3A%20Refresh%20Drop-down%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1470034%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F667968%22%20target%3D%22_blank%22%3E%40peteryac60%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%2C%20Peter%2C%3C%2FP%3E%3CP%3EI%20have%20a%20master%20list.%20All%20important%20data%20is%20stored%20in%20master%20list.%20%3CBR%20%2F%3E%20In%20the%20other%20Excel%20sheets%20A%20and%20B%20there%20are%20dropdowns%20that%20draw%20their%20data%20from%20the%20master%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20now%20select%20the%20data%20for%20Excel%20sheets%20A%20and%20B%20using%20the%20drop-down%20menu.%20%3CBR%20%2F%3E%20The%20data%20was%20transferred%20into%20the%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESuppose%20I%20want%20to%20change%20the%20data%20from%20the%20cells%20on%20Excel%20sheets%20A%20and%20B%20now.%20%3CBR%20%2F%3E%20For%20this%20I%20go%20to%20the%20master%20list%20and%20change%20the%20data%20from%20the%20master%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20I%20want%20the%20data%20in%20the%20cells%20on%20Excel%20sheets%20A%20and%20B%20to%20be%20changed%20automatically.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUntil%20now%2C%20the%20old%20data%20in%20the%20cells%20on%20Excel%20sheets%20A%20and%20B%20remain.%20To%20achieve%20the%20change%2C%20however%2C%20you%20have%20to%20select%20the%20changed%20data%20again%20using%20the%20drop-down%20menu.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20solution%20for%20my%20wish%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewarm%20greetings%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1470084%22%20slang%3D%22en-US%22%3ERe%3A%20Refresh%20Drop-down%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1470084%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F699325%22%20target%3D%22_blank%22%3E%40Lukas_Feix_HAWE%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Lukas%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20the%20'master'%20sheet%20has%20a%20list%20X%2C%20Y%2C%20Z.%3C%2FP%3E%3CP%3EIn%20the%20main%20sheet%20you%20select%20from%20the%20drop%20down%20the%20value%20you%20want%20-%20as%20an%20example%20X.%3C%2FP%3E%3CP%3EIf%20I%20understand%20your%20issue%20you%20want%20to%20change%20the%20drop%20down%20from%20X%2CY%2CZ%20to%20A%2CB%2CC%3F%3C%2FP%3E%3CP%3EYou%20%2Cof%20course%2C%20can%20do%20this.%20On%20the%20main%20sheet%20the%20X%20will%20remain%20until%20you%20select%20one%20of%20A%2CB%20or%20C.%3C%2FP%3E%3CP%3EExcel%20has%20no%20way%20of%20changing%20X%20because%20there%20is%20no%20way%20of%20knowing%20if%20you%20want%20A%2CB%20or%20C%20until%20you%20select.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1585096%22%20slang%3D%22en-US%22%3ERe%3A%20Refresh%20Drop-down%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1585096%22%20slang%3D%22en-US%22%3EIs%20there%20any%20way%20to%20refresh%20a%20drop%20down%20list%20with%20the%20first%20element%20of%20the%20list%3F%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20this%20example%20X%20will%20remain%2C%20but%20is%20there%20any%20way%20to%20said%20excel%20pick%20the%20first%20element%20by%20default%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1586410%22%20slang%3D%22en-US%22%3ERe%3A%20Refresh%20Drop-down%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1586410%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F752998%22%20target%3D%22_blank%22%3E%40GJoe_CI%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20attached%20file%20uses%20some%20VBA%20code%20so%20that%20if%20the%20drop%20down%20values%20changes%20from%20(say)%20X%20Y%20Z%20to%20A%20B%20C%20on%20the%20Master%20sheet%20then%20the%20default%20value%20will%20be%20A%20i.e.%20the%20first%20value%20on%20the%20new%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20-%20there%20are%20limitations.%20Examples%20are%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3Eif%20you%20list%20have%20more%20that%203%20values%20then%20you%20need%20to%20manually%20adjust%20the%20code%3C%2FLI%3E%3CLI%3Ethe%20drop%20down%20list%20is%20in%20cell%20D2.%20If%20you%20need%20it%20to%20be%20in%20another%20cell%2C%20you%20need%20to%20adjust%20the%20code%3C%2FLI%3E%3C%2FUL%3E%3CP%3EThe%20code%20is%20very%20simple%20so%20adjusting%20it%20should%20not%20be%20difficult.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20will%20need%20the%20developer%20tab%20to%20access%20the%20code%20-%20if%20you%20don't%20have%20this%20already%20you%20can%20access%20from%20FILE-%26gt%3BOPTIONS-%26gt%3BCUSTOMIZE%20RIBBON-%26gt%3B%20on%20the%20right%20hand%20pane%20ensure%20the%20DEVELOPER%20option%20is%20checked.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehope%20this%20helps.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

5 Replies

@Lukas_Feix_HAWE 

 

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

 

@peteryac60 

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

@Lukas_Feix_HAWE 

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

 

 

 

 

Is 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?

@GJoe_CI 

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