Excel 2003 - ADDRESS() function issues

Question

I finally thought I had found a way to acutally use excel productively but the code that I followed does not appear to work.

I'm thinking that the code is very limited and can't do what I want but I thought I'd ask to confirm - maybe it is my function that is the problem.

I want to calculate the sum of a row of values for the previous month based on how many days we are into this month (i.e. It is the 20th of April so I want to sum the first 20 days of March to compare against.)

=SUM(G4:ADDRESS(ROW(),7+$BR$3,4))

I basically want to SUM(G4:AA4) and have used the address function to return the cell reference AA4 by taking G4 and adding 20 to the column count.

ADDRESS(ROW(),7+$BR$3,4)

This successfully returns AA7 as expected HOWEVER, when I try to use the returning value in the SUM() function it throws an error...

Am I not able to use this reference in my calculation?

Answer 1

Try the OFFSET function instead:

=SUM(OFFSET(G4,0,0,1,$BR$3))

More info here

Answer 2

You can use indirect Attention in German the names are a bit different: =SUMME(INDIREKT("G4:" & ADRESSE(4;8+19;1;4)))