Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 336
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 336
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default 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


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default 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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 336
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How:Add New Worksheet that copies the cell of the previous sheet JLM Excel Worksheet Functions 1 August 24th 06 08:04 PM
How to change a link to another worksheet by changing a value in a cell? nratanachai Excel Worksheet Functions 1 June 9th 06 09:49 AM
hyperlink - link one cell to another in a worksheet. how? Drewz Excel Worksheet Functions 1 October 20th 05 04:09 PM
How can I link a cell in one worksheet to a cell in another works. EWI_Guy Excel Worksheet Functions 3 April 5th 05 09:15 PM
How do link to a remote worksheet using the path value in a field? Michael T. Links and Linking in Excel 3 December 11th 04 09:45 AM


All times are GMT +1. The time now is 09:55 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"