ADDRESS Function

  • Thread starter Thread starter Hardy
  • Start date Start date
H

Hardy

Hi,

I want to create variable range within a function and ADDRESS function
looks to be the route to do this.

Problem is that it returns the address as text within the fuction, so
with a simple example if cells A1:A5 contain no's 1..5, and B5 has

=SUM(ADDRESS(1,1,1,1,)&":"&ADDRESS(5,1,1,1,)),

The range within sum returns

"$A$1:$A$5", rather than

$A$1:$A$5

and therefore formula does not recognise it as a range.

Any ideas - there must be simple way round.
 
INDIRECT will convert a string into a usable cell or range
reference.

HTH
Jason
Atlanta, GA
 
wrap indirect around it

=SUM(INDIRECT(ADDRESS(1,1,1,1,)&":"&ADDRESS(5,1,1,1,)))

however I would personally use OFFSET

=SUM(OFFSET($A$1,,,5))
 
Hardy > said:
Hi,

I want to create variable range within a function and ADDRESS function
looks to be the route to do this.

Problem is that it returns the address as text within the fuction, so
with a simple example if cells A1:A5 contain no's 1..5, and B5 has

=SUM(ADDRESS(1,1,1,1,)&":"&ADDRESS(5,1,1,1,)),
Click to expand...

You can change your formula into:
=SUM(INDIRECT(ADDRESS(1,1,1,1,)&":"&ADDRESS(5,1,1,1,)))

but, what's about something like:
=SUM(OFFSET($A$1,,,5))
?

Regards
Barbara
 
Problem is that it returns the address as text within the fuction, so
with a simple example if cells A1:A5 contain no's 1..5, and B5 has

=SUM(ADDRESS(1,1,1,1,)&":"&ADDRESS(5,1,1,1,)),

The range within sum returns

"$A$1:$A$5", rather than

$A$1:$A$5
Click to expand...
...

Others have responded with one OFFSET call. In case you need independent corners
for your range,

=SUM(OFFSET(OneCell,a,b,c,d):OFFSET(AnotherCell,w,x,y,z))

would be better than INDIRECT(ADDRESS(...)), though any single area range could
be given by a single OFFSET call.
 
Back
Top