Forum Discussion
using = to get data from a sheet based on a cell name
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.
Fatdave42 , the solution HansVogelaar 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.
5 Replies
Fatdave42 , the solution HansVogelaar 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.
Thank you for pointing out my mistake! I have corrected it.
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.
