Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How:Add New Worksheet that copies the cell of the previous sheet | Excel Worksheet Functions | |||
How to change a link to another worksheet by changing a value in a cell? | Excel Worksheet Functions | |||
hyperlink - link one cell to another in a worksheet. how? | Excel Worksheet Functions | |||
How can I link a cell in one worksheet to a cell in another works. | Excel Worksheet Functions | |||
How do link to a remote worksheet using the path value in a field? | Links and Linking in Excel |