Thanks, Harlan. Very useful info.

Here's what I tried as a workaround:

TEST.XLS Sheet1 column A filled with the respective row numbers.

New file:

A1 = 35000

Formula:

=COUNT(MATCH(A1,'C:\TV\[test.xls]Sheet1'!A1:A15000,0),MATCH(A1,'C:\TV\[test.xls]Sheet1'!A15001:A30000,0),MATCH(A1,'C:\TV\[test.xls]Sheet1'!A30001:A45000,0),MATCH(A1,'C:\TV\[test.xls]Sheet1'!A45001:A60000,0),MATCH(A1,'C:\TV\[test.xls]Sheet1'!A60001:A65536,0))

This still fails as described earlier when TEST.XLS is closed.

So I broke it down into a series of individual formulas:

=COUNT(MATCH(A1,'C:\TV\[test.xls]Sheet1'!A1:A15000,0))
=COUNT(MATCH(A1,'C:\TV\[test.xls]Sheet1'!A15001:A30000,0))
=COUNT(MATCH(A1,'C:\TV\[test.xls]Sheet1'!A30001:A45000,0))
etc
etc

Even this fails!

Biff

"Harlan Grove" wrote in message
oups.com...
"T. Valko" wrote...
It may have something to do with available memory.
...

Not necessarily.

Opened a new file. Entered this simple formula:

=VLOOKUP(A1,'C:\TV\[test.xls]Sheet1'!A:B,2,0)

It works just fine as long as TEXT.XLS is open. When I close
TEXT.XLS I get a popup message to the effect: Excel cannot
complete this operation with available resources......

Excel passes '[test.xls]Sheet1'!A:B as a reference to a range in an
open workbook. OTOH, Excel passes 'C:\TV\[test.xls]Sheet1'!A:B as an
array (perhaps as a reference to an array), but to do so it needs to
create that array by reading the values from the closed workbook.
Since Excel 2003 & prior can't handle arrays spanning 65536 or more
rows, Excel can't handle this operation referencing a closed file.

This operation fails if you use A1:B65535. Heck, it fails if you use
A1:B16376. *BUT* it works without complaining if you use A1:H16375. So
it seems to me this is an old Excel 5 & prior limitation on array size
(number of rows) returned by external references into closed workbooks
that was never updated for the larger grid size in Excel 97 and later.
It has nothing to do with available memory, since most PCs, even those
bought around 2000, would have page files large enough to store arrays
of a hundred thousand rows by a dozen columns. This limitation is
hardcoded into Excel's own source code, just like 7 nested function
calls in cell formulas, 30 arguments in function calls, 255 characters
to define names.

IOW, buy a new machine with more RAM, and you'll still have this
problem.