Fixing a Total on a page
 

I have a spreadsheet that totals up a column at the bottom of each page
(using SUM) so that when it's printed off a total is at the bottom of each
page. However when I edit that sheet and add or delete items, the total
figure is pushed up or down the page and I have to recalculate the total.
When you've got 20 pages in one sheet and you alter page 1 it's a pain when
it messes up every following page.

How can i fix a cell or row of cells so that no matter what i add or delete
above it, that row stays fixed.

e.g with my current page setup (A4 size) row 62 is the bottom row of the
first page, with 124 being the second page and so on. I want to fix row 62 so
that the SUM function in those cells never moves no matter how many additonal
rows i add in above it ?

Fixing a Total on a page
 

You want to use the OFFSET Worksheet Function, see
http://www.mvps.org/dmcritchie/excel/offset.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jimbob" wrote in message ...
I have a spreadsheet that totals up a column at the bottom of each page
(using SUM) so that when it's printed off a total is at the bottom of each
page. However when I edit that sheet and add or delete items, the total
figure is pushed up or down the page and I have to recalculate the total.
When you've got 20 pages in one sheet and you alter page 1 it's a pain when
it messes up every following page.

How can i fix a cell or row of cells so that no matter what i add or delete
above it, that row stays fixed.

e.g with my current page setup (A4 size) row 62 is the bottom row of the
first page, with 124 being the second page and so on. I want to fix row 62 so
that the SUM function in those cells never moves no matter how many additonal
rows i add in above it ?

Fixing a Total on a page
 

I don't think that's working. Let me try another example so you can
understand what i mean.

In cell J62 i have "SUM(J1:J61)"

If i then add in 10 additional rows of figures on that page the sum formula
is pushed down to cell J72. Which when I print out hard copies has pushed my
total on to the next page, which is no good.

I want the "SUM" formula in cell J62 to remain fixed in cell J62 no matter
what happens above it.

Does that make sense ???

If this is what the offset function does I can't make it work.

"David McRitchie" wrote:

You want to use the OFFSET Worksheet Function, see
http://www.mvps.org/dmcritchie/excel/offset.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jimbob" wrote in message ...
I have a spreadsheet that totals up a column at the bottom of each page
(using SUM) so that when it's printed off a total is at the bottom of each
page. However when I edit that sheet and add or delete items, the total
figure is pushed up or down the page and I have to recalculate the total.
When you've got 20 pages in one sheet and you alter page 1 it's a pain when
it messes up every following page.

How can i fix a cell or row of cells so that no matter what i add or delete
above it, that row stays fixed.

e.g with my current page setup (A4 size) row 62 is the bottom row of the
first page, with 124 being the second page and so on. I want to fix row 62 so
that the SUM function in those cells never moves no matter how many additonal
rows i add in above it ?

Fixing a Total on a page
 

J62: =SUM(J$1:OFFSET(J62,-1,0)
as described on pages
http://www.mvps.org/dmcritchie/excel/offset.htm
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jimbob" wrote in message ...
I don't think that's working. Let me try another example so you can
understand what i mean.

In cell J62 i have "SUM(J1:J61)"

If i then add in 10 additional rows of figures on that page the sum formula
is pushed down to cell J72. Which when I print out hard copies has pushed my
total on to the next page, which is no good.

I want the "SUM" formula in cell J62 to remain fixed in cell J62 no matter
what happens above it.

Does that make sense ???

If this is what the offset function does I can't make it work.

"David McRitchie" wrote:

You want to use the OFFSET Worksheet Function, see
http://www.mvps.org/dmcritchie/excel/offset.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jimbob" wrote in message ...
I have a spreadsheet that totals up a column at the bottom of each page
(using SUM) so that when it's printed off a total is at the bottom of each
page. However when I edit that sheet and add or delete items, the total
figure is pushed up or down the page and I have to recalculate the total.
When you've got 20 pages in one sheet and you alter page 1 it's a pain when
it messes up every following page.

How can i fix a cell or row of cells so that no matter what i add or delete
above it, that row stays fixed.

e.g with my current page setup (A4 size) row 62 is the bottom row of the
first page, with 124 being the second page and so on. I want to fix row 62 so
that the SUM function in those cells never moves no matter how many additonal
rows i add in above it ?

Fixing a Total on a page
 

File/ Page Setup/ Page/ fit to 1 page tall?
--
David Biddulph

"Jimbob" wrote in message
...
I don't think that's working. Let me try another example so you can
understand what i mean.

In cell J62 i have "SUM(J1:J61)"

If i then add in 10 additional rows of figures on that page the sum
formula
is pushed down to cell J72. Which when I print out hard copies has pushed
my
total on to the next page, which is no good.

I want the "SUM" formula in cell J62 to remain fixed in cell J62 no matter
what happens above it.

Does that make sense ???

If this is what the offset function does I can't make it work.

"David McRitchie" wrote:

You want to use the OFFSET Worksheet Function, see
http://www.mvps.org/dmcritchie/excel/offset.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jimbob" wrote in message
...
I have a spreadsheet that totals up a column at the bottom of each page
(using SUM) so that when it's printed off a total is at the bottom of
each
page. However when I edit that sheet and add or delete items, the total
figure is pushed up or down the page and I have to recalculate the
total.
When you've got 20 pages in one sheet and you alter page 1 it's a pain
when
it messes up every following page.

How can i fix a cell or row of cells so that no matter what i add or
delete
above it, that row stays fixed.

e.g with my current page setup (A4 size) row 62 is the bottom row of
the
first page, with 124 being the second page and so on. I want to fix row
62 so
that the SUM function in those cells never moves no matter how many
additonal
rows i add in above it ?

Fixing a Total on a page
 

No that does not work. Even by typing =SUM(J$1:OFFSET(J62,-1,0), if i then
add in 10 extra lines above it, the above formula is pushed down 10 lines to
J72.

I think this may be more of a page formating problem than a formula problem.



"David McRitchie" wrote:



J62: =SUM(J$1:OFFSET(J62,-1,0)
as described on pages
http://www.mvps.org/dmcritchie/excel/offset.htm
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jimbob" wrote in message ...
I don't think that's working. Let me try another example so you can
understand what i mean.

In cell J62 i have "SUM(J1:J61)"

If i then add in 10 additional rows of figures on that page the sum formula
is pushed down to cell J72. Which when I print out hard copies has pushed my
total on to the next page, which is no good.

I want the "SUM" formula in cell J62 to remain fixed in cell J62 no matter
what happens above it.

Does that make sense ???

If this is what the offset function does I can't make it work.

"David McRitchie" wrote:

You want to use the OFFSET Worksheet Function, see
http://www.mvps.org/dmcritchie/excel/offset.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jimbob" wrote in message ...
I have a spreadsheet that totals up a column at the bottom of each page
(using SUM) so that when it's printed off a total is at the bottom of each
page. However when I edit that sheet and add or delete items, the total
figure is pushed up or down the page and I have to recalculate the total.
When you've got 20 pages in one sheet and you alter page 1 it's a pain when
it messes up every following page.

How can i fix a cell or row of cells so that no matter what i add or delete
above it, that row stays fixed.

e.g with my current page setup (A4 size) row 62 is the bottom row of the
first page, with 124 being the second page and so on. I want to fix row 62 so
that the SUM function in those cells never moves no matter how many additonal
rows i add in above it ?

Fixing a Total on a page
 

Yes, obviously you're right. If you have a formula in J62 and you ask Excel
to insert 10 cells above it and to move the remaining cells down, then
your cell will move to J72; that's what you've asked it to do. If you
don't want it to move, don't insert cells above it. If you want the "page
totals" to stay in rows 62, 124, etc., then your best bet is to put them in
a separate column from where you're putting your extra data. In other words
put your SUM formula in column K, adding up the values in column J as has
been suggested. You can then insert extra data in column J without it
moving your totals in column K.
--
David Biddulph

"Jimbob" wrote in message
...
No that does not work. Even by typing =SUM(J$1:OFFSET(J62,-1,0), if i then
add in 10 extra lines above it, the above formula is pushed down 10 lines
to
J72.

I think this may be more of a page formating problem than a formula
problem.



"David McRitchie" wrote:



J62: =SUM(J$1:OFFSET(J62,-1,0)
as described on pages
http://www.mvps.org/dmcritchie/excel/offset.htm
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jimbob" wrote in message
...
I don't think that's working. Let me try another example so you can
understand what i mean.

In cell J62 i have "SUM(J1:J61)"

If i then add in 10 additional rows of figures on that page the sum
formula
is pushed down to cell J72. Which when I print out hard copies has
pushed my
total on to the next page, which is no good.

I want the "SUM" formula in cell J62 to remain fixed in cell J62 no
matter
what happens above it.

Does that make sense ???

If this is what the offset function does I can't make it work.

"David McRitchie" wrote:

You want to use the OFFSET Worksheet Function, see
http://www.mvps.org/dmcritchie/excel/offset.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jimbob" wrote in message
...
I have a spreadsheet that totals up a column at the bottom of each
page
(using SUM) so that when it's printed off a total is at the bottom
of each
page. However when I edit that sheet and add or delete items, the
total
figure is pushed up or down the page and I have to recalculate the
total.
When you've got 20 pages in one sheet and you alter page 1 it's a
pain when
it messes up every following page.

How can i fix a cell or row of cells so that no matter what i add
or delete
above it, that row stays fixed.

e.g with my current page setup (A4 size) row 62 is the bottom row
of the
first page, with 124 being the second page and so on. I want to fix
row 62 so
that the SUM function in those cells never moves no matter how many
additonal
rows i add in above it ?

Fixing a Total on a page
 

OK i'll have to use that method, but that still leaves me with a problem.

Say i use worksheet 2 to have a summary page and total up all the pages on
sheet 1 e.g =Sum(sheet1!j1:sheet1!j62) and the same for page 2
=sum(sheet1!j63-sheet1!j124) and so on, as soon as I add or delete lines in
sheet 1 the formulas on sheet 2 get all messed up, again if i added ten lines
the formula would alter to this =sum(sheet1!j1:sheet1!j72). There must be a
way of getting the formula to ignore cell moves and stay fixed.

"David Biddulph" wrote:

Yes, obviously you're right. If you have a formula in J62 and you ask Excel
to insert 10 cells above it and to move the remaining cells down, then
your cell will move to J72; that's what you've asked it to do. If you
don't want it to move, don't insert cells above it. If you want the "page
totals" to stay in rows 62, 124, etc., then your best bet is to put them in
a separate column from where you're putting your extra data. In other words
put your SUM formula in column K, adding up the values in column J as has
been suggested. You can then insert extra data in column J without it
moving your totals in column K.
--
David Biddulph

"Jimbob" wrote in message
...
No that does not work. Even by typing =SUM(J$1:OFFSET(J62,-1,0), if i then
add in 10 extra lines above it, the above formula is pushed down 10 lines
to
J72.

I think this may be more of a page formating problem than a formula
problem.



"David McRitchie" wrote:



J62: =SUM(J$1:OFFSET(J62,-1,0)
as described on pages
http://www.mvps.org/dmcritchie/excel/offset.htm
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jimbob" wrote in message
...
I don't think that's working. Let me try another example so you can
understand what i mean.

In cell J62 i have "SUM(J1:J61)"

If i then add in 10 additional rows of figures on that page the sum
formula
is pushed down to cell J72. Which when I print out hard copies has
pushed my
total on to the next page, which is no good.

I want the "SUM" formula in cell J62 to remain fixed in cell J62 no
matter
what happens above it.

Does that make sense ???

If this is what the offset function does I can't make it work.

"David McRitchie" wrote:

You want to use the OFFSET Worksheet Function, see
http://www.mvps.org/dmcritchie/excel/offset.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jimbob" wrote in message
...
I have a spreadsheet that totals up a column at the bottom of each
page
(using SUM) so that when it's printed off a total is at the bottom
of each
page. However when I edit that sheet and add or delete items, the
total
figure is pushed up or down the page and I have to recalculate the
total.
When you've got 20 pages in one sheet and you alter page 1 it's a
pain when
it messes up every following page.

How can i fix a cell or row of cells so that no matter what i add
or delete
above it, that row stays fixed.

e.g with my current page setup (A4 size) row 62 is the bottom row
of the
first page, with 124 being the second page and so on. I want to fix
row 62 so
that the SUM function in those cells never moves no matter how many
additonal
rows i add in above it ?

Fixing a Total on a page
 

That's where you use the OFFSET formula as previously suggested.
--
David Biddulph


"Jimbob" wrote in message
...
OK i'll have to use that method, but that still leaves me with a problem.

Say i use worksheet 2 to have a summary page and total up all the pages on
sheet 1 e.g =Sum(sheet1!j1:sheet1!j62) and the same for page 2
=sum(sheet1!j63-sheet1!j124) and so on, as soon as I add or delete lines
in
sheet 1 the formulas on sheet 2 get all messed up, again if i added ten
lines
the formula would alter to this =sum(sheet1!j1:sheet1!j72). There must be
a
way of getting the formula to ignore cell moves and stay fixed.

"David Biddulph" wrote:

Yes, obviously you're right. If you have a formula in J62 and you ask
Excel
to insert 10 cells above it and to move the remaining cells down, then
your cell will move to J72; that's what you've asked it to do. If you
don't want it to move, don't insert cells above it. If you want the
"page
totals" to stay in rows 62, 124, etc., then your best bet is to put them
in
a separate column from where you're putting your extra data. In other
words
put your SUM formula in column K, adding up the values in column J as has
been suggested. You can then insert extra data in column J without it
moving your totals in column K.
--
David Biddulph

"Jimbob" wrote in message
...
No that does not work. Even by typing =SUM(J$1:OFFSET(J62,-1,0), if i
then
add in 10 extra lines above it, the above formula is pushed down 10
lines
to
J72.

I think this may be more of a page formating problem than a formula
problem.



"David McRitchie" wrote:



J62: =SUM(J$1:OFFSET(J62,-1,0)
as described on pages
http://www.mvps.org/dmcritchie/excel/offset.htm
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jimbob" wrote in message
...
I don't think that's working. Let me try another example so you can
understand what i mean.

In cell J62 i have "SUM(J1:J61)"

If i then add in 10 additional rows of figures on that page the sum
formula
is pushed down to cell J72. Which when I print out hard copies has
pushed my
total on to the next page, which is no good.

I want the "SUM" formula in cell J62 to remain fixed in cell J62 no
matter
what happens above it.

Does that make sense ???

If this is what the offset function does I can't make it work.

"David McRitchie" wrote:

You want to use the OFFSET Worksheet Function, see
http://www.mvps.org/dmcritchie/excel/offset.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:
http://www.mvps.org/dmcritchie/excel/search.htm

"Jimbob" wrote in message
...
I have a spreadsheet that totals up a column at the bottom of
each
page
(using SUM) so that when it's printed off a total is at the
bottom
of each
page. However when I edit that sheet and add or delete items,
the
total
figure is pushed up or down the page and I have to recalculate
the
total.
When you've got 20 pages in one sheet and you alter page 1 it's
a
pain when
it messes up every following page.

How can i fix a cell or row of cells so that no matter what i
add
or delete
above it, that row stays fixed.

e.g with my current page setup (A4 size) row 62 is the bottom
row
of the
first page, with 124 being the second page and so on. I want to
fix
row 62 so
that the SUM function in those cells never moves no matter how
many
additonal
rows i add in above it ?

Fixing a Total on a page
 

To expand a bit on my answer, where you've said you'd use the formula
=Sum(sheet1!j1:sheet1!j62)
=sum(sheet1!j63-sheet1!j124) and so on
then try
=SUM(OFFSET(sheet1!K1,0,-1):OFFSET(sheet1!K62,0,-1))
and copy this down as
=SUM(OFFSET(sheet1!K63,0,-1):OFFSET(sheet1!K124,0,-1)) and so on

You can then insert extra cells in J without it upsetting K in the way you
suggested.
--
David Biddulph

"David Biddulph" wrote in message
...
That's where you use the OFFSET formula as previously suggested.

"Jimbob" wrote in message
...
OK i'll have to use that method, but that still leaves me with a problem.

Say i use worksheet 2 to have a summary page and total up all the pages
on
sheet 1 e.g =Sum(sheet1!j1:sheet1!j62) and the same for page 2
=sum(sheet1!j63-sheet1!j124) and so on, as soon as I add or delete lines
in
sheet 1 the formulas on sheet 2 get all messed up, again if i added ten
lines
the formula would alter to this =sum(sheet1!j1:sheet1!j72). There must
be a
way of getting the formula to ignore cell moves and stay fixed.

"David Biddulph" wrote:

Yes, obviously you're right. If you have a formula in J62 and you ask
Excel
to insert 10 cells above it and to move the remaining cells down, then
your cell will move to J72; that's what you've asked it to do. If you
don't want it to move, don't insert cells above it. If you want the
"page
totals" to stay in rows 62, 124, etc., then your best bet is to put them
in
a separate column from where you're putting your extra data. In other
words
put your SUM formula in column K, adding up the values in column J as
has
been suggested. You can then insert extra data in column J without it
moving your totals in column K.
--
David Biddulph

"Jimbob" wrote in message
...
No that does not work. Even by typing =SUM(J$1:OFFSET(J62,-1,0), if i
then
add in 10 extra lines above it, the above formula is pushed down 10
lines
to
J72.

I think this may be more of a page formating problem than a formula
problem.



"David McRitchie" wrote:



J62: =SUM(J$1:OFFSET(J62,-1,0)
as described on pages
http://www.mvps.org/dmcritchie/excel/offset.htm
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jimbob" wrote in message
...
I don't think that's working. Let me try another example so you can
understand what i mean.

In cell J62 i have "SUM(J1:J61)"

If i then add in 10 additional rows of figures on that page the sum
formula
is pushed down to cell J72. Which when I print out hard copies has
pushed my
total on to the next page, which is no good.

I want the "SUM" formula in cell J62 to remain fixed in cell J62 no
matter
what happens above it.

Does that make sense ???

If this is what the offset function does I can't make it work.

"David McRitchie" wrote:

You want to use the OFFSET Worksheet Function, see
http://www.mvps.org/dmcritchie/excel/offset.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jimbob" wrote in message
...
I have a spreadsheet that totals up a column at the bottom of
each
page
(using SUM) so that when it's printed off a total is at the
bottom
of each
page. However when I edit that sheet and add or delete items,
the
total
figure is pushed up or down the page and I have to recalculate
the
total.
When you've got 20 pages in one sheet and you alter page 1 it's
a
pain when
it messes up every following page.

How can i fix a cell or row of cells so that no matter what i
add
or delete
above it, that row stays fixed.

e.g with my current page setup (A4 size) row 62 is the bottom
row
of the
first page, with 124 being the second page and so on. I want to
fix
row 62 so
that the SUM function in those cells never moves no matter how
many
additonal
rows i add in above it ?