Reference to a different spreadsheet

[Guest]

Hi. I have multiple spreadsheets and a "consolidated" sheet that ties them
all together. Right now, I have a formula in each cell that refers back to a
different spreadsheet to get the
data(=+'L:\Budget\2008\Susan\[Susan2008Budget.xls]Consolidated'!C10)

That is working fine, but what I would like to do is have the name "Susan"
in a cell and have the formula refer to the cell that the name "Susan" is in
to create the formula. So, if I change where I want the data to come from, I
only have to change the name in the cell that "Susan" is in. So if I changed
Susan to Mike, the formula would read:
=+'L:\Budget\2008\Mike\[Mike2008Budget.xls]Consolidated'!C10

Any ideas?

 

[Guest]

Use the INDIRECT function. Let's say your vaiable name is stored in cell A1.

=INDIRECT("L:\Budget\2008\"&$A$1&"\["&$A$1&"2008Budget.xls]Consolidated!C10)

HTH,
Elkar

 

[Guest]

I think we are close, thank you, but I seem to be getting an formula error,
not sure why..

Use the INDIRECT function. Let's say your vaiable name is stored in cell A1.

=INDIRECT("L:\Budget\2008\"&$A$1&"\["&$A$1&"2008Budget.xls]Consolidated!C10)

HTH,
Elkar

Hi. I have multiple spreadsheets and a "consolidated" sheet that ties them
all together. Right now, I have a formula in each cell that refers back to a
different spreadsheet to get the
data(=+'L:\Budget\2008\Susan\[Susan2008Budget.xls]Consolidated'!C10)

That is working fine, but what I would like to do is have the name "Susan"
in a cell and have the formula refer to the cell that the name "Susan" is in
to create the formula. So, if I change where I want the data to come from, I
only have to change the name in the cell that "Susan" is in. So if I changed
Susan to Mike, the formula would read:
=+'L:\Budget\2008\Mike\[Mike2008Budget.xls]Consolidated'!C10

Any ideas?

 

[Harlan Grove]

Use the INDIRECT function. Let's say your vaiable name is stored in
cell A1.

=INDIRECT("L:\Budget\2008\"&$A$1&"\["&$A$1&"2008Budget.xls]Consolidated!C10)

....

INDIRECT for external references ONLY works when the other workbooks
are open in the same Excel instance. However, when workbooks are open,
Excel doesn't require (and doesn't display) the drive/directory path
because it can only open one file with a given base filename at a
time. Therefore, if the OP's workbooks would be open, the formula may
be shortened to

=INDIRECT("'["&$A$1&"2008Budget.xls]Consolidated'!C10")

However, if these other files would be closed, your INDIRECT formula
will return #REF! errors.

There are work-arounds.

http://www.google.com/[email protected]

 

[Guest]

Correct, the other workbooks will not be open, so I will have to work with a
different approach.

Use the INDIRECT function. Let's say your vaiable name is stored in
cell A1.

=INDIRECT("L:\Budget\2008\"&$A$1&"\["&$A$1&"2008Budget.xls]Consolidated!C10)

....

INDIRECT for external references ONLY works when the other workbooks
are open in the same Excel instance. However, when workbooks are open,
Excel doesn't require (and doesn't display) the drive/directory path
because it can only open one file with a given base filename at a
time. Therefore, if the OP's workbooks would be open, the formula may
be shortened to

=INDIRECT("'["&$A$1&"2008Budget.xls]Consolidated'!C10")

However, if these other files would be closed, your INDIRECT formula
will return #REF! errors.

There are work-arounds.

http://www.google.com/[email protected]