summing a column up to a point
 

I am having trouble with a budget i have made.
I have created fromulas that are simple and just add up invoice amounts in a
total row (simple addition sum(a1:13)), the problem is that when one of my
staff need to enter a new invoice they insert a line right above the total
line and it is not included in the formula as it is now located in a14. Is
there a way to make the sum include all rows up to the formula cell?

does this make sense

summing a column up to a point
 

Is it at all possible to have the total above the rows....You will need to
insert a row.

And enter a formula in the new A1
=sum(A2:A1000)
1000 rows enough?
--
Greetings from New Zealand
Bill K

"Heather" wrote in message
...
I am having trouble with a budget i have made.
I have created fromulas that are simple and just add up invoice amounts in
a
total row (simple addition sum(a1:13)), the problem is that when one of my
staff need to enter a new invoice they insert a line right above the total
line and it is not included in the formula as it is now located in a14.
Is
there a way to make the sum include all rows up to the formula cell?

does this make sense

summing a column up to a point
 

Bill thanks for your suggestion, the only problem being that i have two
sub-categories in each column that need to be totalled separately (they are
above one another) and the totalled completely, so it is impossible to fill
in a set amount for each section, i was hoping for bit more flexible
formula...maybe i am dreaming

"Bill Kuunders" wrote:

Is it at all possible to have the total above the rows....You will need to
insert a row.

And enter a formula in the new A1
=sum(A2:A1000)
1000 rows enough?
--
Greetings from New Zealand
Bill K

"Heather" wrote in message
...
I am having trouble with a budget i have made.
I have created fromulas that are simple and just add up invoice amounts in
a
total row (simple addition sum(a1:13)), the problem is that when one of my
staff need to enter a new invoice they insert a line right above the total
line and it is not included in the formula as it is now located in a14.
Is
there a way to make the sum include all rows up to the formula cell?

does this make sense

summing a column up to a point
 

assuming the sum is located in a13, then name a13 as e.g. pos.
using this name, put a formula like
=SUM(INDIRECT("a1:a"&ROW(pos)-1))
in a13.

will this work for you?

keizi

"Heather" wrote in message
...
I am having trouble with a budget i have made.
I have created fromulas that are simple and just add up invoice amounts in a
total row (simple addition sum(a1:13)), the problem is that when one of my
staff need to enter a new invoice they insert a line right above the total
line and it is not included in the formula as it is now located in a14. Is
there a way to make the sum include all rows up to the formula cell?

does this make sense

summing a column up to a point
 

Hi Heather,

It sounds like you need a dynamic formula, so when rows are inserted above
the totals row it includes them in the calculation. If this is what you're
after then follow these steps.

The following example is for a sheet named "Sheet1", with a formula in "A14"
to sum "A1:A13":

1. Select cell "A14"
2. Define a local name as "'Sheet1'!LastCell"
3. Highlight the contents of the RefersTo box, and select cell "A13"
4. Remove the "$" sign from the address.

Doing this makes the named cell relative to the cell containing the formula
as one row above. As rows are inserted above the totals row, they will be
included in the calculation. You must revise your formula as follows:

=SUM(A1:LastCell)

Good Luck!
GS

"Heather" wrote:

I am having trouble with a budget i have made.
I have created fromulas that are simple and just add up invoice amounts in a
total row (simple addition sum(a1:13)), the problem is that when one of my
staff need to enter a new invoice they insert a line right above the total
line and it is not included in the formula as it is now located in a14. Is
there a way to make the sum include all rows up to the formula cell?

does this make sense

summing a column up to a point
 

"kounoike" wrote in message
...
assuming the sum is located in a13, then name a13 as e.g. pos.
using this name, put a formula like
=SUM(INDIRECT("a1:a"&ROW(pos)-1))
in a13.

above is redundant, no need to use name, just
=SUM(INDIRECT("a1:a"&ROW()-1))
would work for me.

keizi