SOLVED

using = to get data from a sheet based on a cell name

%3CLINGO-SUB%20id%3D%22lingo-sub-2508974%22%20slang%3D%22en-US%22%3Eusing%20%3D%20to%20get%20data%20from%20a%20sheet%20based%20on%20a%20cell%20name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2508974%22%20slang%3D%22en-US%22%3E%3CP%3EHi.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%26nbsp%3B%3DHYPERLINK(%22%23'%22%26amp%3BA5%26amp%3B%22'!A1%22%2CA5)%20to%20make%20a%20link%20to%20a%20sheet%20with%20the%20sheet%20name%20in%20cell%20A5.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20works%20very%20whell%20and%20save%20me%20doing%20a%20link%20for%20every%20new%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20would%20like%20to%20do%20next%20is%20get%20the%20data%20from%20cell%20F1%20(in%20a%20sheet%20that%20is%20named%20in%20A5%20on%20the%20master))%20to%20display%20on%20ny%20master.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20whne%20I%20make%20a%20new%20sheet%2C%20all%20I%20need%20to%20do%20is%20put%20the%20sheet%20name%20in%20a%20cell%20in%20my%20mastr%20sheet%2C%20and%20the%20link%20is%20then%20there%2C%20along%20with%20the%20information%20from%202%20cells%20in%20the%20new%20sheet.%20Using%20the%20logic%20in%20the%20Hyperlink%20it%20would%20be%26nbsp%3B%3D%22%23'%22%26amp%3BA5%26amp%3B%22'!F1%20but%20this%20does%20not%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20it%20can%20be%20done%20using%20%3DSheetname!F1%2C%20but%20i%20dont%20want%20to%20type%20the%20information%20in%20eatch%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2508974%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2509022%22%20slang%3D%22en-US%22%3ERe%3A%20using%20%3D%20to%20get%20data%20from%20a%20sheet%20based%20on%20a%20cell%20name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2509022%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1093534%22%20target%3D%22_blank%22%3E%40Fatdave42%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUse%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DHYPERLINK(%22%23'%22%26amp%3BA5%26amp%3B%22'!A1%22%2CINDIRECT(%22'%22%26amp%3BA5%26amp%3B'!F1%22))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2509026%22%20slang%3D%22en-US%22%3ERe%3A%20using%20%3D%20to%20get%20data%20from%20a%20sheet%20based%20on%20a%20cell%20name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2509026%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20not%20after%20a%20hyperlink%20to%20the%20information%2C%20i%20am%20just%20after%20the%20information%20to%20show%20on%20the%20master%20sheet%2C%20but%20using%20the%20sheet%20name%20that%20is%20on%20the%20master%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi.

 

I am using =HYPERLINK("#'"&A5&"'!A1",A5) to make a link to a sheet with the sheet name in cell A5.

 

This works very whell and save me doing a link for every new sheet.

 

What I would like to do next is get the data from cell F1 (in a sheet that is named in A5 on the master)) to display on ny master.

 

So whne I make a new sheet, all I need to do is put the sheet name in a cell in my mastr sheet, and the link is then there, along with the information from 2 cells in the new sheet. Using the logic in the Hyperlink it would be ="#'"&A5&"'!F1 but this does not work.

 

I know it can be done using =Sheetname!F1, but i dont want to type the information in eatch time.

 

 

5 Replies

@Fatdave42 

Use

 

=HYPERLINK("#'"&A5&"'!A1",INDIRECT("'"&A5&"'!F1"))

 

I am not after a hyperlink to the information, i am just after the information to show on the master sheet, but using the sheet name that is on the master sheet.

best response confirmed by allyreckerman (Microsoft)
Solution

@Fatdave42 , the solution @Hans Vogelaar gave will do that.  More specifically the 

INDIRECT("'" & A5 & "'!F1")

portion will return the value from cell F1 on the sheet named in cell A5.

Note: I did find a typo in the formula missing a quote so that might have been part of your problem.

@mtarler 

Thank you for pointing out my mistake! I have corrected it.

@Hans Vogelaar and @mtarler 

 

Many thanks for your help. That is spot on.

 

Thanks agin.