Data Table Questions

R

RMires

I need to create data tables in which I can manipulate the data to a great
extent. I have found that the tables themselves are best created in Access,
but the calculations I need are quite extensive, and best performed in Excel.
The problem with Excel tables is that they are open ended: I keep running
daily totals, and the records for one day may be 2, 10 or 100. I have found
that linking Access and Excel files to be quite difficul, especially in the
newer versions. Many problems could be solved by finding the answer to one
question:

Is there a way to manipulate data in an Excel table without having to
reference the last row, or record in the table? I have seen similar questions
but the only few answers seem to resort to some formula "finding" the cell
reference of the last row. Can I for example, sum all of the numbers in a
particular column of a table (a named range perhapsJ?) without referencing
the exact end of the column?
 
R

Roger Govier

Hi

If the formula is not in column A then
=SUM(A:A)
or if you want the formula in column A then
Insert a row a the top of column A and in cell A1
=SUBTOTAL(9,A:A)
or
=SUM(OFFSET(A2,0,0,COUNTA(A2:A65536))
 
Top