SOLVED

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

Copper 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.

1 best response

Accepted Solutions
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.

View solution in original post