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?

You can create dynamic named ranges if that's what you're looking for.

See he http://www.ozgrid.com/Excel/DynamicRanges.htm
--
Brevity is the soul of wit.


"RMires" wrote:

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?

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))

--
Regards

Roger Govier


"RMires" wrote in message
...
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?