Thanks, Ron, for all your great help.

If you (or anyone else) has any other ideas how to achieve the same result,
I welcome them.

"Ron Rosenfeld" wrote:

On Sun, 17 Dec 2006 08:29:00 -0800, hmm wrote:

Thanks Ron.

I tried again, this time checking carefully all quotes.

Suppose cells A1 through A10 the numbers 1 to 10.

The formula =SUM(INDIRECT.EXT("'C:\[MyBook.xls]MySheet'!$A$1:$A$10"))
will indeed return the sum of all 10 cells (55), even when MyBook.xls is
closed.

However, suppose I only want a subrange of 3 cells beginning with A2. The
formula

=SUM(OFFSET(INDIRECT.EXT("'C:\[MyBook.xls]MySheet'!$A$1:$A$10"),1,0,3))

only works (gives the correct result of 9) when MyBook.xls is open. When
MyBook.xls is closed, I get the "#VALUE!" error

(Since the limits are calculated in another formula, it is not possible to
enter the subrange explicitly as "A2:A4".)

Any other ideas?

Well, the problem seems to be that, when the workbook is closed, INDIRECT.EXT
(as described in the HELP section), returns the "VALUES" of those cells. These
values are returned as an array.

The OFFSET function, on the other hand, requires that the first argument be a
cell reference, not an array of values. Hence the VALUE error.

It is interesting that if the workbook is open, INDIRECT.EXT returns the cell
reference.


--ron