Excel automatically adds path to external reference -> formula too long

[Roland]

Excel 2000 SR1 / Windows 2000

I have moved a large Excel application to a new folder. However this creates
the following problem:
I have many external references to other Excel files located in the SAME
folder.
Fields with references such as:
'[File1.XLS]Sheet1'!C50 + '[File1.XLS]Sheet2'!C50 + ...
are automatically converted by excel into:
'D:\Full path\[File1.XLS]Sheet1'!C50 + 'D:\Full path\[File1.XLS]Sheet2'!C50
+ ...
This results finally into errors, since the formula becomes to long to fit
into a single field.

Can anyone please give me a hint, how to turn this automatism off?

Thanks
Roland

 

[Harlan Grove]

...
...

Fields with references such as:
'[File1.XLS]Sheet1'!C50 + '[File1.XLS]Sheet2'!C50 + ...
are automatically converted by excel into:
'D:\Full path\[File1.XLS]Sheet1'!C50 + 'D:\Full path\[File1.XLS]Sheet2'!C50
+ ...
This results finally into errors, since the formula becomes to long to fit
into a single field.

Can anyone please give me a hint, how to turn this automatism off?

There is no way to turn this functionality off.

However, if the portion of the formula above is indicative, you should rewrite
them (with the other workbook open before opening the workbook containing these
formulas) as

=SUM('[File1.XLS]Sheet1:SheetN'!C50)

If that isn't practical (the worksheets in question aren't in sequence), then
your only alternative is entering external references to individual cells in
separate cells in this workbook (possibly in a hidden worksheet), then summing
the range of those external references.

 

[Roland]

Dear Harlan
thanks a lot for your assistance.
When the referenced files are open, Excel does not automatically add the
path name.I can work this way a s a workaround.
I also tried the much more elegant Sum formula you recommended. However I
could not make it work. I assume that this is because the referenced sheets
have special characters in their name, i.e. KW (1) ... KW (52) . Excel does
not issue an error message, but does not produce any values either.
=SUMME('[PG166.XLS]KW (1):KW (27)'!C91)
Have a great day
Roland

...
..

Fields with references such as:
'[File1.XLS]Sheet1'!C50 + '[File1.XLS]Sheet2'!C50 + ...
are automatically converted by excel into:
'D:\Full path\[File1.XLS]Sheet1'!C50 + 'D:\Full path\[File1.XLS]Sheet2'!C50
+ ...
This results finally into errors, since the formula becomes to long to fit
into a single field.

Can anyone please give me a hint, how to turn this automatism off?

There is no way to turn this functionality off.

However, if the portion of the formula above is indicative, you should rewrite
them (with the other workbook open before opening the workbook containing these
formulas) as

=SUM('[File1.XLS]Sheet1:SheetN'!C50)

If that isn't practical (the worksheets in question aren't in sequence), then
your only alternative is entering external references to individual cells in
separate cells in this workbook (possibly in a hidden worksheet), then summing
the range of those external references.

 

[Harlan Grove]

...
...

When the referenced files are open, Excel does not automatically add the
path name.I can work this way a s a workaround.

Excel has odd external reference semantics. One of Excel's oldest and at times
most frustrating limitations is it's inability to allow you to open multiple
files with the same base filename. That is, D:\foo\X.xls and C:\bar\X.xls can't
be open at the same time. If D:\foo\X.xls were open, this allows Excel to refer
to that file only by its base filename as in [X.xls]. Since only one file may be
open with the base filename X.xls, there's no ambiguity.

When workbooks are closed, however, Excel can no longer refer to that file just
by its base filename because then there would be ambiguity with respect to which
exact file it was referring. That's why Excel must include the entire pathname
in references to closed files. There is no work-around for this.

I also tried the much more elegant Sum formula you recommended. However I
could not make it work. I assume that this is because the referenced sheets
have special characters in their name, i.e. KW (1) ... KW (52) . Excel does
not issue an error message, but does not produce any values either.
=SUMME('[PG166.XLS]KW (1):KW (27)'!C91)

Excel can accomodate parentheses in worksheet names in English versions, so it
can also accomodate them formulas with references to those worksheets. For
example, the following formula works for me.

=SUM('[deleteme.xls]foo (1):foo (4)'!A1)

I suppose it's possible your language version (German?) could use a character
other than colon ) to separate worksheet names. If you select a blank cell,
start typing the formula =SUMME( then press [Crtl]+[PageDown] followed by
[Shift]+[Ctrl]+[PageDown] a few times followed by ) and [Enter], what does
your formula look like? What's the character between the worksheet names? If
it's something other than :, then replace the : in your SUMME formula with this
other character.

FWIW, while Excel can accept square brackets in worksheet names, you'll find it
impossible to enter formulas containing external references to cells in so-named
worksheets. At least in Excel 97. I wonder if this is fixed in more recent
versions (by disallowing square brackets in worksheet names).

 

[Dave Peterson]

xl2002 still allows square brackets in the worksheet name.

Harlan Grove wrote:

 

[Roland]

Dear Harlan
thanks alot for your detailed explanation. Now even the strange behaviour of
Excel even makes sense.
Youer guess was tight, I'm working with the German version ef Excel. I tried
your suggestions and finally could make it work
Example: =SUMME('D:\full_path\[filename.XLS]KW (40):KW (52)'!C50)
Thanks again for your great help
Roland

...
..

When the referenced files are open, Excel does not automatically add the
path name.I can work this way a s a workaround.

Excel has odd external reference semantics. One of Excel's oldest and at times
most frustrating limitations is it's inability to allow you to open multiple
files with the same base filename. That is, D:\foo\X.xls and C:\bar\X.xls can't
be open at the same time. If D:\foo\X.xls were open, this allows Excel to refer
to that file only by its base filename as in [X.xls]. Since only one file may be
open with the base filename X.xls, there's no ambiguity.

When workbooks are closed, however, Excel can no longer refer to that file just
by its base filename because then there would be ambiguity with respect to which
exact file it was referring. That's why Excel must include the entire pathname
in references to closed files. There is no work-around for this.

I also tried the much more elegant Sum formula you recommended. However I
could not make it work. I assume that this is because the referenced sheets
have special characters in their name, i.e. KW (1) ... KW (52) . Excel does
not issue an error message, but does not produce any values either.
=SUMME('[PG166.XLS]KW (1):KW (27)'!C91)

Excel can accomodate parentheses in worksheet names in English versions, so it
can also accomodate them formulas with references to those worksheets. For
example, the following formula works for me.

=SUM('[deleteme.xls]foo (1):foo (4)'!A1)

I suppose it's possible your language version (German?) could use a character
other than colon ) to separate worksheet names. If you select a blank cell,
start typing the formula =SUMME( then press [Crtl]+[PageDown] followed by
[Shift]+[Ctrl]+[PageDown] a few times followed by ) and [Enter], what does
your formula look like? What's the character between the worksheet names? If
it's something other than :, then replace the : in your SUMME formula with this
other character.

FWIW, while Excel can accept square brackets in worksheet names, you'll find it
impossible to enter formulas containing external references to cells in so-named
worksheets. At least in Excel 97. I wonder if this is fixed in more recent
versions (by disallowing square brackets in worksheet names).