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