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?

"Ron Rosenfeld" wrote:

On Sun, 17 Dec 2006 06:37:00 -0800, hmm wrote:

INDIRECT.EXT, from Laurent Longre's Morefunc 4.2 Add-in, is giving me a
"#VALUE!" error message in the cell with the formula:

=SUM(INDIRECT.EXT("[MyBook.xls]MySheet!$A$1:$A$3"))

when MyBook.xls is closed; it only works when I open MyBook.xls.

From what I've seen in the help file and other posts in this forum, it is
supposed to work for closed workbooks and for ranges of cells.

How can I get it to work? Perhaps there is an update, or a way of using
this function that I'm not aware of?

Thanks.

I believe the problem is your syntax. You included the function required
double quotes, but omitted the "single quotes" that are part of the Excel
required naming convention.

=SUM(INDIRECT.EXT("'[MyBook.xls]MySheet'!$A$1:$A$3"))

Also, although you did not mention it one way or the other, I believe the
formula you posted will execute faster as an array formula.


--ron