Reference a worksheet in a cell

[lynxbci]

I have a main worksheet that pulls data from one of several other worksheets, which may not be open at that time. I would like to be able to type into cell the name of the worksheet that i wish to use. Is this possible?

E.g
In cell A1 you type worksheet1.xls
In Cell A3 the Formula ='[WORKSHEET1.xls]Sheet1'!$A$5
If i then change A1 to workseet2.xls i want the formula in A3 to change to
='[WORKSHEET2.xls]Sheet1'!$A$5

Something like = A3!Sheet1!$A$5 but this doesn't work

thanks in advance

 

[Andrew Poulsom]

You could try:

=INDIRECT("'["&A3&"]Sheet1'!$A$5")

but the source workbook must be open.

 

[nbrcrunch]

=HYPERLINK("[C:\myPath\TAPv2r1.xls]Setup!$E$7","This Page")

The above link is correctly formatted to allow Excel to not only open a file but to access a specific cell on a specific tab.

Pay special attention to the punctuation/syntactical marks.

Caveat: If you have already used the menu system to create a link and then attempt entering the hyperlink function on top of it, the old link will take precedence. Since the menuing system-supported linking does not support referencing a specific tab and cell, you will continue to experience problems. The solution is to first remove the link and then use the hyperlink command.