Dynamic Summing

[Paul]

I have a workbook, where the data is arranged with headings in the first row
and data every other. New columns are added to the worksheet every month (2
in fact). A summary worksheet sums the data by row, but only for the last
row of the data-worksheet. IE If the last column is X, then the summary
worksheet will sum X2:X10. Next month, the new data will be in Z, thus I
need the summary worksheet to sum Z2:Z10.

Here are my criteria:
* The number of rows to be summed is dependant on the number of items in
column A
* Column A contains blank cells
* The number of columns in the data-worksheet will vary with time
* The summary worksheet contains a formula in A1 that sums the LATEST
column.

I need a dynamic range name that will be the latest column, from row 2 to
the last row in column A.

Can someone help me with a worksheet formula?

This is what I have done so far:

=OFFSET(OFFSET(A1,0,COUNTA($1:$1)-1,1,1),0,0,COUNTA($A:$A),1)

but it does not handle blank cells in column A.

COUNTA(A:A) simply counts the number of non-blank cells in column A, as you
have found. This ARRAY formula gives the row number of the last non-blank
cell in A1:A100:
=MAX(NOT(ISBLANK(A1:A100))*ROW(A1:A100))
Note that you cannot use a whole column reference, and you must array-enter
it.
Similarly, for the column number of the last non-blank cell in A1:J1:
=MAX(NOT(ISBLANK(A1:J1))*COLUMN(A1:J1))

I don't see why you are trying to use two OFFSET functions. I would have
thought something like this would do what you want:
=OFFSET(A1,0,0,MAX(NOT(ISBLANK(A1:A100))*ROW(A1:A100)),MAX(NOT(ISBLANK(A1:J1
))*COLUMN(A1:J1)))

 

[Julian Milano]

I have a workbook, where the data is arranged with headings in the first row
and data every other. New columns are added to the worksheet every month (2
in fact). A summary worksheet sums the data by row, but only for the last
row of the data-worksheet. IE If the last column is X, then the summary
worksheet will sum X2:X10. Next month, the new data will be in Z, thus I
need the summary worksheet to sum Z2:Z10.

Here are my criteria:
* The number of rows to be summed is dependant on the number of items in
column A
* Column A contains blank cells
* The number of columns in the data-worksheet will vary with time
* The summary worksheet contains a formula in A1 that sums the LATEST
column.

I need a dynamic range name that will be the latest column, from row 2 to
the last row in column A.

Can someone help me with a worksheet formula?

This is what I have done so far:

=OFFSET(OFFSET(A1,0,COUNTA($1:$1)-1,1,1),0,0,COUNTA($A:$A),1)

but it does not handle blank cells in column A.

Thank you.