Known bug? range with 2 file paths

  • Thread starter Thread starter DAN
  • Start date Start date
D

DAN

We have been struggling with a buggy spreadsheet, and finally traced the problem
to a double-path range as follows:

A reference to an external range is normally defined by a formula like
'mypath\[myfile.xls]mytab'!cell1:cell2

Somehow, we had a series of cells with the range reference displayed as
'mypath\[myfile.xls]mytab'!cell1:'mypath\[myfile.xls]mytab'!cell2

Excel does not object (we have tried to enter it manually and XL accepts it),
and all goes well as long as the referenced sheet (myfile.xls) is also open.
When you try later to open the sheet containing this formula without opening the
referenced file, updateLinkedInfo:No, the formula always produces an error #REF!

(there are lots of weird secondary effects, too)

Is this a known bug?
Is there any sensible use of a second filepath for the end cell of a range?


Should anyone ever meet this case, a simple cure is to replace the
chain _:'mypath\[myfile.xls]mytab'!_ by the chain _:_ on the whole sheet.
HTH.
DAN

Environment: Excel 2kSR1 (9.0.4402 SR-1) on Win2kSP3
 
I can't think of a good reason to show that reference twice. And one of the
biggest reasons not to do it is to keep that formula length small(er).

Excel's formulas can be 1024 characters long when you're in R1C1 mode. Any
chance you're exceeding that length?

In minor tests in xl2002, the formula evaluated correctly without opening the
linked workbook.


We have been struggling with a buggy spreadsheet, and finally traced the problem
to a double-path range as follows:

A reference to an external range is normally defined by a formula like
'mypath\[myfile.xls]mytab'!cell1:cell2

Somehow, we had a series of cells with the range reference displayed as
'mypath\[myfile.xls]mytab'!cell1:'mypath\[myfile.xls]mytab'!cell2

Excel does not object (we have tried to enter it manually and XL accepts it),
and all goes well as long as the referenced sheet (myfile.xls) is also open.
When you try later to open the sheet containing this formula without opening the
referenced file, updateLinkedInfo:No, the formula always produces an error #REF!

(there are lots of weird secondary effects, too)

Is this a known bug?
Is there any sensible use of a second filepath for the end cell of a range?

Should anyone ever meet this case, a simple cure is to replace the
chain _:'mypath\[myfile.xls]mytab'!_ by the chain _:_ on the whole sheet.
HTH.
DAN

Environment: Excel 2kSR1 (9.0.4402 SR-1) on Win2kSP3
Click to expand...
 
Hi Dave,
I can't think of a good reason to show that reference twice.
Click to expand...
Same here.
Excel's formulas can be 1024 characters long when you're in R1C1 mode. Any
chance you're exceeding that length?
Click to expand...
The formula is 245 chars long. And we're not in R1C1 mode.


DAN
 
How does the formula look? Some functions will not work with
closed workbooks like OFFSET, INDIRECT, COUNTIF and SUMIF
for example
 
Peo said:
How does the formula look? Some functions will not work with
closed workbooks like OFFSET, INDIRECT, COUNTIF and SUMIF
for example
Click to expand...


A row is like:
=VLOOKUP("PBX"&Q5,'mypath\[myfile.xls]mytab'!$A$4:$A$119,15,FALSE)

another row is:
=COUNTIF('mypath\[myfile.xls]mytab'!$A$4:$A$119,"PBX"&Q5)

In both, the closed worksheet problem exists if the path is repeated before
$A$119; all is fine if it isn't.

DAN
 
Peo wins!

Your =countif() function won't work with a closed workbook.

You could use =sumproduct(), though:
=COUNTIF('mypath\[myfile.xls]mytab'!$A$4:$A$119,"PBX"&Q5)

I've never had a problem with =vlookup, though. But this may be your problem:
You're trying to bring back column 15 in the A4:A119 range. There's only one
column there.

But that would be an when both workbooks are open. (Probably a typo in the
newsgroup posting????)


Peo said:
How does the formula look? Some functions will not work with
closed workbooks like OFFSET, INDIRECT, COUNTIF and SUMIF
for example
Click to expand...

A row is like:
=VLOOKUP("PBX"&Q5,'mypath\[myfile.xls]mytab'!$A$4:$A$119,15,FALSE)

another row is:
=COUNTIF('mypath\[myfile.xls]mytab'!$A$4:$A$119,"PBX"&Q5)

In both, the closed worksheet problem exists if the path is repeated before
$A$119; all is fine if it isn't.

DAN
Click to expand...
 
Dave said:
Peo wins!

Your =countif() function won't work with a closed workbook.

You could use =sumproduct(), though:
=COUNTIF('mypath\[myfile.xls]mytab'!$A$4:$A$119,"PBX"&Q5)

I've never had a problem with =vlookup, though. But this may be your problem:
You're trying to bring back column 15 in the A4:A119 range. There's only one
column there.
Click to expand...
But that would be an when both workbooks are open. (Probably a typo in the
newsgroup posting????)
Click to expand...

Definitely my bad. Don't know how I screwed this up in the post. The real range
is A4:S119.


DAN
 
Back
Top