address function

[peter]

L1 has the formula
=ADDRESS(4,MATCH(L$2,$3:$3,0))
This returns $g$4

L2 has the value 08

Range d3 – j3 has the values
D e f g h I j
Row 3 07,07,07,08,08,08,08
Row 4 23,22,21,23,21,21,22

L4 as the formula
=SUM(OFFSET(INDIRECT(L$1),0,0,1,COUNTIF($3:$3,L$2)))
This returns 87

This is all very well.

When I try to replace the indirect portion of L4 with the address formula
from L1 it errors.

=SUM(OFFSET(ADDRESS(4,MATCH(L$2,$3:$3,0)),0,0,1,COUNTIF($3:$3,L$2)))
?????

Any ideas???
Peter

 

[T. Valko]

That's because the ADDRESS function returns a TEXT string. A TEXT string is
not a valid reference even if it looks like one.

 

[RagDyer]

Why would you want to replace the concise "Indirect(L1)" with the much
longer Address() formula?

Are you looking to eliminate the need for using cell L1, or are you just
looking at this as a topic of conversation?

To eliminate the need for L1, simply wrap the Address formula in Indirect,
as you did previously with the "Indirect(L1)" reference:

=SUM(OFFSET(INDIRECT(ADDRESS(4,MATCH(L$2,$3:$3,0))),0,0,1,COUNTIF($3:$3,L$2)))

I would suspect that you are aware of this, since you did use Indirect with
L1 in the first place.