D
DaveO
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.
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.