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.

 

[MyVeryOwnSelf]

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?

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.

 

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