Fixing a Total on a page

[Guest]

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 ?

 

[David McRitchie]

You want to use the OFFSET Worksheet Function, see
http://www.mvps.org/dmcritchie/excel/offset.htm

 

[Guest]

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]

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

 

[David Biddulph]

File/ Page Setup/ Page/ fit to 1 page tall?

 

[Guest]

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 Biddulph]

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.

 

[Guest]

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]

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

 

[David Biddulph]

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.