excel problem: sum(address(4,2,1):address(2,1,1))
 

I want to construct ranges using the address function, but it comes out
invalid. An example is
sum (address(4,2,1):address(20,2,1))
or
sum (address(e7,2,1):address(e8,2,1))

but I keep getting that this is invalid.
The general idea is to be able to use another cell to tell me where a range
begins and ends. I was able to do this years ago in 123, but excel has
trouble with it.
How can I accomplish this?

excel problem: sum(address(4,2,1):address(2,1,1))
 

On Thu, 21 Dec 2006 18:21:01 -0800, DrSlowpoke
wrote:

I want to construct ranges using the address function, but it comes out
invalid. An example is
sum (address(4,2,1):address(20,2,1))
or
sum (address(e7,2,1):address(e8,2,1))

but I keep getting that this is invalid.
The general idea is to be able to use another cell to tell me where a range
begins and ends. I was able to do this years ago in 123, but excel has
trouble with it.
How can I accomplish this?


The ADDRESS worksheet function returns a string, not a cell reference. From
HELP: "Creates a cell address as text"


So if you want to create a function to do what you describe above, you need
something like:

=SUM(INDIRECT(ADDRESS(4,2,1)&":"&ADDRESS(20,2,1)))


--ron

excel problem: sum(address(4,2,1):address(2,1,1))
 

Try one of these:

=SUM(INDIRECT(ADDRESS(4,2,1)&":"&ADDRESS(20,2,1)))

E7 = 4
E8 = 20

=SUM(INDIRECT(ADDRESS(E7,2,1)&":"&ADDRESS(E8,2,1)) )

=SUM(INDIRECT("B"&E7):INDEX(B:B,E8))

=SUM(B4:INDEX(B:B,E8))

Biff

"DrSlowpoke" wrote in message
...
I want to construct ranges using the address function, but it comes out
invalid. An example is
sum (address(4,2,1):address(20,2,1))
or
sum (address(e7,2,1):address(e8,2,1))

but I keep getting that this is invalid.
The general idea is to be able to use another cell to tell me where a
range
begins and ends. I was able to do this years ago in 123, but excel has
trouble with it.
How can I accomplish this?