Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing Workbook Name in Formula
I need to set up a formula that will allow me to specify a specific
workbook and worksheet name as part of a relative reference. I can refer to a cell on a specific sheet within a specific workbook using: =[Book.xls]Sheet1!A1 I can accomplish the same thing using: =INDIRECT(CONCATENATE("'[",$X$1,"]",$X$2,"'!",”A1”)) Whe Cell X1 – Book1.xls Cell X2 – Sheet1 If I copy/paste the first expression ‘A1’ will behave as a relative reference however the second expression is effectively: =[Book.xls]Sheet1!$A$1 Is there any way I can define the formula with the workbook and worksheet names defined in the specified cells, X1 and X2 in the example, and still have the ‘A1’ portion behave as a relative reference? Thanks, Russ D. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing Workbook Name in Formula
I need to set up a formula that will allow me to specify a specific
workbook and worksheet name as part of a relative reference. I can refer to a cell on a specific sheet within a specific workbook using: =[Book.xls]Sheet1!A1 I can accomplish the same thing using: =INDIRECT(CONCATENATE("'[",$X$1,"]",$X$2,"'!",”A1”)) Whe Cell X1 – Book1.xls Cell X2 – Sheet1 If I copy/paste the first expression ‘A1’ will behave as a relative reference however the second expression is effectively: =[Book.xls]Sheet1!$A$1 Is there any way I can define the formula with the workbook and worksheet names defined in the specified cells, X1 and X2 in the example, and still have the ‘A1’ portion behave as a relative reference? Instead of "A1", you might try using the functions ROW() and COLUMN(), adding or subtracting an offset as needed, in constructing the required cell reference. Notice that setting the second argument of INDIRECT to FALSE invokes R1C1 style references, avoiding the need to do arithmetic on alphabetic character codes for the first part of the cell reference. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing Workbook Name in Formula
You don't need to use CONCATENATE to put text together. Try this (off the
top of my head) formula.... =INDIRECT("["&$X$1&"]"&$X$2&"!"&A1) Rick ************************************ "Russ" wrote in message ... I need to set up a formula that will allow me to specify a specific workbook and worksheet name as part of a relative reference. I can refer to a cell on a specific sheet within a specific workbook using: =[Book.xls]Sheet1!A1 I can accomplish the same thing using: =INDIRECT(CONCATENATE("'[",$X$1,"]",$X$2,"'!",”A1”)) Whe Cell X1 – Book1.xls Cell X2 – Sheet1 If I copy/paste the first expression ‘A1’ will behave as a relative reference however the second expression is effectively: =[Book.xls]Sheet1!$A$1 Is there any way I can define the formula with the workbook and worksheet names defined in the specified cells, X1 and X2 in the example, and still have the ‘A1’ portion behave as a relative reference? Thanks, Russ D. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing Workbook Name in Formula
If you are copying down, so you want the row number to change, then
you can do it like this: =INDIRECT("'[" & $X$1 & "]" & $X$2 & "'!A" & ROW(A1)) The ROW(A1) term (returning 1) becomes ROW(A2), ROW(A3), ROW(A4) etc as it is copied down, thus returning 2, 3, 4 etc. Hope this helps. Pete On May 13, 8:13*pm, Russ wrote: I need to set up a formula that will allow me to specify a specific workbook and worksheet name as part of a relative reference. I can refer to a cell on a specific sheet within a specific workbook using: =[Book.xls]Sheet1!A1 I can accomplish the same thing using: =INDIRECT(CONCATENATE("'[",$X$1,"]",$X$2,"'!",”A1”)) Whe Cell X1 – Book1.xls Cell X2 – Sheet1 If I copy/paste the first expression ‘A1’ will behave as a relative reference however the second expression is effectively: =[Book.xls]Sheet1!$A$1 Is there any way I can define the formula with the workbook and worksheet names defined in the specified cells, X1 and X2 in the example, and still have the ‘A1’ portion behave as a relative reference? Thanks, Russ D. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing Workbook Name in Formula
On May 13, 4:31 pm, Pete_UK wrote:
If you are copying down, so you want the row number to change, then you can do it like this: =INDIRECT("'[" & $X$1 & "]" & $X$2 & "'!A" & ROW(A1)) The ROW(A1) term (returning 1) becomes ROW(A2), ROW(A3), ROW(A4) etc as it is copied down, thus returning 2, 3, 4 etc. Hope this helps. Pete On May 13, 8:13 pm, Russ wrote: I need to set up a formula that will allow me to specify a specific workbook and worksheet name as part of a relative reference. I can refer to a cell on a specific sheet within a specific workbook using: =[Book.xls]Sheet1!A1 I can accomplish the same thing using: =INDIRECT(CONCATENATE("'[",$X$1,"]",$X$2,"'!",”A1”)) Whe Cell X1 – Book1.xls Cell X2 – Sheet1 If I copy/paste the first expression ‘A1’ will behave as a relative reference however the second expression is effectively: =[Book.xls]Sheet1!$A$1 Is there any way I can define the formula with the workbook and worksheet names defined in the specified cells, X1 and X2 in the example, and still have the ‘A1’ portion behave as a relative reference? Thanks, Russ D. Thanks for the help. The formula now creates a relative reference to the correct sheet and updates whenever I change the specified workbook name in cell X1. The only problem now is that the specified workbook needs to be open to extract the data. I 'm trying to build a single summary sheet that can extract data from several workbooks without the need to open each individual workbook whenever I change the filename in cell X1. I tried adding the full directory path: C:\data\Sheet1.xls But the cell values still become #REF whenever I close the source workbook. Any ideas? Thanks, Russ D. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing other workbook. | Excel Worksheet Functions | |||
VLOOKUP referencing another workbook | Excel Discussion (Misc queries) | |||
Referencing a different Workbook | Excel Worksheet Functions | |||
Referencing another Workbook | Excel Discussion (Misc queries) | |||
formula referencing another workbook | Excel Worksheet Functions |