=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
--
Gary's Student


"Martin" wrote:

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!

"Gary''s Student" wrote:

Su

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.
--
Gary''s Student


"Martin" wrote:

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