INDIRECT.EXT with vector reference

[Guest]

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.

 

[Ron Rosenfeld]

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

 

[Guest]

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?

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

 

[Ron Rosenfeld]

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

 

[Guest]

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.

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

 

[Ron Rosenfeld]

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.

The problem, if I understand what you are doing, is to return an array of
values from an array of values.

One way to do that would be with the LOOKUP function.

In your case, assume the text form of the address is in A1.

=INDIRECT.EXT(A1) would then return an array containing the values in the range
specified.

Let us say you want to sum the 2nd, 3rd and 4th items in that array.

A formula of the sort:

=SUM(LOOKUP({2,3,4},ROW(INDIRECT("1:"&
COUNT(INDIRECT.EXT(A1)))),INDIRECT.EXT(A1)))

would sum the 2nd, 3rd and 4th items returned by INDIRECT.EXT(a1).

This is the vector form of the LOOKUP function.

I believe it can be entered either normally or as an array formula. For speed,
Longre advises using the array method of entry.

Without knowing more about your specifications, it's hard to advise you how to
compute the array {2,3,4}, but some variant of the ROW(INDIRECT(...)) method
should work. e.g. =ROW(INDIRECT(start & num_of_entries)) instead of the
computation you are using for the OFFSET function.

There may be simpler methods of doing this. I have not searched the NG for it,
though.
--ron