i have an excel sheet with data in several columns and hundreds of rows that
i am calculating a subtotal for when i filter the data. i want the subtotal
formula as the top row of the spreadsheet so that when the filter is applied
the change in results is visible. i will be adding rows to the bottom of the
spreadsheet and want to define a dynamic range for the column so that i do
not have to change the subtotal formula range every time a new group of rows
is added, but i keep getting circular reference errors when doing this using
the offset formula to name the group of cells. is there a way to define a
name for the last cell in a column that has data in it that would not create
a circular reference?

You can set your subtotal range to near the end of the sheet:
=SUBTOTAL(9,A2:A50000)

As long as your dataset does not exceed 50,000 rows, you are set. If it
does, just adjust the range again. Really, there is nothing stopping you
from setting it to
SUBTOTAL(9,A2:A65536) for Excel 2003 or earlier
or
SUBTOTAL(9,A2:A1048576) for Excel 2007. There may be a performance issue,
but only if you have a complex array.

--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.



"Starboxes" wrote:

i have an excel sheet with data in several columns and hundreds of rows that
i am calculating a subtotal for when i filter the data. i want the subtotal
formula as the top row of the spreadsheet so that when the filter is applied
the change in results is visible. i will be adding rows to the bottom of the
spreadsheet and want to define a dynamic range for the column so that i do
not have to change the subtotal formula range every time a new group of rows
is added, but i keep getting circular reference errors when doing this using
the offset formula to name the group of cells. is there a way to define a
name for the last cell in a column that has data in it that would not create
a circular reference?

yeah i'd thought of that but didn't want it to have to come to that since i
will not be the person using the sheet in another month...guess i'm stuck!
thanks :)

"PJFry" wrote:

You can set your subtotal range to near the end of the sheet:
=SUBTOTAL(9,A2:A50000)

As long as your dataset does not exceed 50,000 rows, you are set. If it
does, just adjust the range again. Really, there is nothing stopping you
from setting it to
SUBTOTAL(9,A2:A65536) for Excel 2003 or earlier
or
SUBTOTAL(9,A2:A1048576) for Excel 2007. There may be a performance issue,
but only if you have a complex array.

--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.



"Starboxes" wrote:

i have an excel sheet with data in several columns and hundreds of rows that
i am calculating a subtotal for when i filter the data. i want the subtotal
formula as the top row of the spreadsheet so that when the filter is applied
the change in results is visible. i will be adding rows to the bottom of the
spreadsheet and want to define a dynamic range for the column so that i do
not have to change the subtotal formula range every time a new group of rows
is added, but i keep getting circular reference errors when doing this using
the offset formula to name the group of cells. is there a way to define a
name for the last cell in a column that has data in it that would not create
a circular reference?