Sum - Offset - Address problem.

[Guest]

I have a growing table of data in Excel, in length, not width. Each month I
get a new table which will have that month's data to also be included in my
report.

I could obviously use a SUM() function at a suitable distance from the
bottom of the data as it stands to do this work for me, however I'm looking
for a permanent solution.

I have a 'Total' row as marked in the cell in (A) & x where x is the actual
row it's in.

I've done a MATCH() to get this row number into a cell, which I can then
use. What I'm now trying to do is sum a range of cells on that row but having
problems. I know the number of columns I want to sum and with the MATCH()
know what row it is in. I use A1 style notation, but can't seem to get the
right combination of functions to work for me.

I've tried =SUM(OFFSET(ADDRESS(A501,1)&":"&ADDRESS(A501,3), 0, 0, 0, 3)
but it returns a #VALUE error {cell A501 currently holds my MATCH() formula
for ease of use whilst testing}. I'm a little puzzled as to how to get this
to work.

Any help gratefully receieved.

TIA.

 

[Bob Phillips]

Maybe

=SUM(OFFSET(A1,0,0,X-1,3)


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)