Link to worksheet whose name is stored in another cell

[Martin]

I want to reference a cell in another worksheet. The name of the worksheet
however changes each month and is stored in another cell.

Can I link to a cell in the second worksheet by referencing the name stored
in a cell in the first one?

Any help is much appreciated!
Martin

 

[Gary''s Student]

Sure:

lets say in Sheet1 cell A1 contains Sheet2 or any other sheet name, then

=INDIRECT(A1 & "!B2") will return the value in Cell B2 in that sheet.

 

[Martin]

Hi,

Thanks your reply. I don't think I made it that clear exactly what i'm after
though.

I have a workbook called Accounts. In this cell A1 contains AUG06.
I want a cell in this workbook to reference a cell stored in another
workbook whose name is AUG06 ACCOUNTS.

I want it to reference the cell so every month i can just over type the
month (i.e. SEPT06) and it will reference the new file.
I need it to look at the external filename but make that name up from the
contents of a cell?

Hope that makes more sense. I really appreciate any help at all!

Thank you!

 

[Dave Peterson]

Maybe this would be better.

=INDIRECT("'" & A1 & "'!B2")

If the name needed those surrounding apostrophes (maybe spaces in the name),
then this will work. And if the apostrophes aren't required, they don't hurt.

 

[Dave Peterson]

The function you'd want to use is =indirect(), but that only works when the
sending workbook is open.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

(I've never used it, though.)

 

[Gary''s Student]

=INDIRECT("'[" & A1 & " ACCOUNTS.xls" & "]" & "Sheet1'!B2")

Please note the placement of the single and double quotes. This takes the
contents of cell A1 and makes the filename. It assumes you want to use
Sheet1 and Cell B2

 

[Martin]

That worked a treat. Thank you!!

Maybe this would be better.

=INDIRECT("'" & A1 & "'!B2")

If the name needed those surrounding apostrophes (maybe spaces in the name),
then this will work. And if the apostrophes aren't required, they don't hurt.