Logical File Reference

[billdierker]

I've created a workbook ("File1") with links to cells in another
workbook ("File2"). What troubles me is that the character string
"File2" appears in numerous cells - as expected and wherever it's used
as a link. It's no surprise or mystery! What I'm looking to accomplish
is to define the character string in a single cell within the first
file and then use that cell in place of the "hard coded" string. Truth
be told, the name of File2 is subject to change over time and the
inherent maintenance associated with this potential is unacceptable.
Simply stated, my requirement is that the linkage to external files be
stored in a single place in the subject workbook to ensure minimal
maintenance when the filename changes.

So, within File1, I want to create a cell with a character string of
something like "File2.xls" in cell A1 on worksheet named 'X'. Then,
within the File1 workbook wherever the string File2.xls is needed,
simply refer to 'X'!A1. For me of course, this is much more complex
than it appears and is currently beyond my capabilities! Help!

I started experimenting with INDIRECT and was able to work out
something close to what I wanted - I'm unable to get my approach
working in every scenario. BTW - I have a handle on the limitation/
restrictions associated with linked workbooks being opened or closed
and that is not a critical success factor. My current example, with
the character string "File2.xls" stored in cell A1 on the current
worksheet, the following formula worked but had a limitation that it
did not copy properly to subsequent cells:

=INDIRECT("'["&$A$1&"]Sheet1'!B1")

This example properly evaluated to provide the value in the linked
workbook, from the proper sheet & cell. Of course, if I dragged this
formula to replicate it to related cells (for instance, B1 is January,
B2 is February, etc. B12 is December), it does not copy to reference
B2, followed by B3, etc. but rather remains "as-is" for each
replication. Naturally, this behavior is expected since the target-cell
portion of the INDIRECT reference is a text string itself. So, that's
my first snag.

The second snag with this approach is in regard to 3D references. I
reviewed another forum thread and my takeawy is that INDIRECT cannot
handle "3D references" in this manner. Again, with the current example
slightly modified to sum the same cell location from multiple,
contiguous worksheets,

=SUM(INDIRECT("'["&$A$1&"]Sheet1:Sheet7'!B1"))

If I dispense with the INDIRECT referencing and use hard coded
"File2.xls", the SUM works as expected, summing the values of cell B1
across sheets 1 through 7 inclusive in the target workbook. Or, in
other words, if the formula is changed to

=SUM('[File2.xls]Sheet1:Sheet7'!B1)

it works dandy. Introducing the INDIRECT reference (no pun intended)
puts the kibosh on the result.

So, that's the story and I'm looking for some help. I'm not married to
any particular solution. In summary, here are my requirements:

- filenames are stored in 1 cell in the subject workbook
- single-point maintenance when the filenames of linked workbooks are
changed
- the solution must accommodate ALL types of formulas/ functions

This may not even be doable but, I thought I'd ask the experts!

Thanks!
Bill

 

[Ron]

Try using a range named X and defining it as ="[Book2]Sheet1!"

Then use the formula =INDIRECT(X&CELL("address",E7))

I think this is a work around that addresses the dragging the formula down
problem.

Ron

I've created a workbook ("File1") with links to cells in another
workbook ("File2"). What troubles me is that the character string
"File2" appears in numerous cells - as expected and wherever it's used
as a link. It's no surprise or mystery! What I'm looking to accomplish
is to define the character string in a single cell within the first
file and then use that cell in place of the "hard coded" string. Truth
be told, the name of File2 is subject to change over time and the
inherent maintenance associated with this potential is unacceptable.
Simply stated, my requirement is that the linkage to external files be
stored in a single place in the subject workbook to ensure minimal
maintenance when the filename changes.

So, within File1, I want to create a cell with a character string of
something like "File2.xls" in cell A1 on worksheet named 'X'. Then,
within the File1 workbook wherever the string File2.xls is needed,
simply refer to 'X'!A1. For me of course, this is much more complex
than it appears and is currently beyond my capabilities! Help!

I started experimenting with INDIRECT and was able to work out
something close to what I wanted - I'm unable to get my approach
working in every scenario. BTW - I have a handle on the limitation/
restrictions associated with linked workbooks being opened or closed
and that is not a critical success factor. My current example, with
the character string "File2.xls" stored in cell A1 on the current
worksheet, the following formula worked but had a limitation that it
did not copy properly to subsequent cells:

=INDIRECT("'["&$A$1&"]Sheet1'!B1")

This example properly evaluated to provide the value in the linked
workbook, from the proper sheet & cell. Of course, if I dragged this
formula to replicate it to related cells (for instance, B1 is January,
B2 is February, etc. B12 is December), it does not copy to reference
B2, followed by B3, etc. but rather remains "as-is" for each
replication. Naturally, this behavior is expected since the target-cell
portion of the INDIRECT reference is a text string itself. So, that's
my first snag.

The second snag with this approach is in regard to 3D references. I
reviewed another forum thread and my takeawy is that INDIRECT cannot
handle "3D references" in this manner. Again, with the current example
slightly modified to sum the same cell location from multiple,
contiguous worksheets,

=SUM(INDIRECT("'["&$A$1&"]Sheet1:Sheet7'!B1"))

If I dispense with the INDIRECT referencing and use hard coded
"File2.xls", the SUM works as expected, summing the values of cell B1
across sheets 1 through 7 inclusive in the target workbook. Or, in
other words, if the formula is changed to

=SUM('[File2.xls]Sheet1:Sheet7'!B1)

it works dandy. Introducing the INDIRECT reference (no pun intended)
puts the kibosh on the result.

So, that's the story and I'm looking for some help. I'm not married to
any particular solution. In summary, here are my requirements:

- filenames are stored in 1 cell in the subject workbook
- single-point maintenance when the filenames of linked workbooks are
changed
- the solution must accommodate ALL types of formulas/ functions

This may not even be doable but, I thought I'd ask the experts!

Thanks!
Bill

 

[billdierker]

Hi Ron. I'm not sure if I'm doing anything wrong - your suggestion did
not work for me in any capacity. It reports a #REF! error. When I step
through Evaluate Formula, it appears to work as expected... The named
range "X" is evaluated first. The CELL result is evaluated next.
Finally, the 2 are concatonated together and the syntax appears good
however, the result is #REF!.

In any event, I appreciate your assistance!

Perhaps my requirements simply cannot be achieved within the framework
of Excel.

Thanks!
Bill

 

[billdierker]

Thank you Harlan!

I quickly reviewed your response and need time to consider it - you
certainly know this material!

Thanks again!
Bill