Link to worksheet whose name is stored in another cell
 

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

Link to worksheet whose name is stored in another cell
 

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

Link to worksheet whose name is stored in another cell
 

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

Link to worksheet whose name is stored in another cell
 

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.

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

--

Dave Peterson

Link to worksheet whose name is stored in another cell
 

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

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

--

Dave Peterson

Link to worksheet whose name is stored in another cell
 

=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

Link to worksheet whose name is stored in another cell
 

That worked a treat. Thank you!! :D

"Dave Peterson" wrote:

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.

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

--

Dave Peterson