On Mon, 18 Dec 2006 00:06:01 -0800, hmm wrote:

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