SOLVED

Autopopulate cells based on different sheets using drop down

%3CLINGO-SUB%20id%3D%22lingo-sub-2865756%22%20slang%3D%22en-US%22%3EAutopopulate%20cells%20based%20on%20different%20sheets%20using%20drop%20down%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2865756%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20wanting%20to%20have%20a%20home%20page%20that%20users%20can%20use%20a%20drop%20down%20on%20to%20display%20'final%20value%20data'%20from%20different%20years%20(user%20selects%202019%2C%20it%20populates%20with%202019%20data).%20I%20have%20seperate%20sheets%20for%20each%20year%20where%20the%20'final%20value%20data'%20is%20calculated.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFrom%20searching%20around%20I%20found%20that%20it's%20possible%20to%20do%20this%20using%20VLOOKUP%20from%20a%20table%20(that%20I%20could%20have%20hidden%20on%20the%20homepage%20or%20on%20a%20seperate%20sheet)%2C%20however%20I'm%20wondering%20if%20there's%20anyway%20to%20do%20this%20without%20creating%20a%20table%20that%20I%20have%20to%20update%20with%20each%20years%20'final%20value%20data'.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENormally%20you%20would%20call%20a%20cell%20from%20a%20seperate%20sheet%20by%20doing%20-%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D'Sheet_name'!Cell_address%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ebut%20can%20I%20get%20it%20to%20change%20the%20sheet%20name%20based%20on%20what%20the%20drop%20down%20menu%20has%20selected%3F%20This%20way%20I%20wouldn't%20have%20to%20make%20a%20table%20that%20is%20updated%20every%20year%2C%20instead%20it%20would%20just%20call%20from%20the%20same%20cell%20but%20on%20different%20sheets%20based%20on%20the%20year%20that%20is%20chosen.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHopefully%20this%20makes%20sense%20and%20someone%20can%20let%20me%20know%20what's%20possible%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2865756%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2865932%22%20slang%3D%22en-US%22%3ERe%3A%20Autopopulate%20cells%20based%20on%20different%20sheets%20using%20drop%20down%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2865932%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1191025%22%20target%3D%22_blank%22%3E%40AndrewBarlee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20a%20drop-down%20with%20the%20sheet%20names%2C%20you%20can%20use%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DINDIRECT(%22'%22%26amp%3Bdropdown_cell%26amp%3B%22'!cell_address%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2869117%22%20slang%3D%22en-US%22%3ERe%3A%20Autopopulate%20cells%20based%20on%20different%20sheets%20using%20drop%20down%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2869117%22%20slang%3D%22en-US%22%3EThats%20it!%20Thanks%20so%20much%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

 

I'm wanting to have a home page that users can use a drop down on to display 'final value data' from different years (user selects 2019, it populates with 2019 data). I have seperate sheets for each year where the 'final value data' is calculated. 

 

From searching around I found that it's possible to do this using VLOOKUP from a table (that I could have hidden on the homepage or on a seperate sheet), however I'm wondering if there's anyway to do this without creating a table that I have to update with each years 'final value data'. 

 

Normally you would call a cell from a seperate sheet by doing - 

='Sheet_name'!Cell_address

but can I get it to change the sheet name based on what the drop down menu has selected? This way I wouldn't have to make a table that is updated every year, instead it would just call from the same cell but on different sheets based on the year that is chosen.

 

Hopefully this makes sense and someone can let me know what's possible

 

2 Replies
best response confirmed by AndrewBarlee (New Contributor)
Solution

@AndrewBarlee 

If you have a drop-down with the sheet names, you can use

 

=INDIRECT("'"&dropdown_cell&"'!cell_address")
Thats it! Thanks so much