Referencing Workbook Name in Formula

[Russ]

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

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

 

[Rick Rothstein \(MVP - VB\)]

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


************************************
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”))

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

 

[Pete_UK]

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

 

[Russ]

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

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

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