![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
I have a spreadsheet that I need to print and send to the government.
When I print it, I need to print a page subtotal and also repeat that
subtotal at the top of the next page.
Is there some way to do this apart from doing it all by hand?
Thanks, Dave
Thanks for that. It's actually the whole problem I need help with.
Starting with how do I print a page subtotal?
And then how do I print some calculated data at the top of a page?
Please keep replies on-list.
Thanks, Dave
Wade Smart wrote:
Hi 
You can set-up various things in the "Header" and/or "Footer" sections of
all pages. Such things as the;
* path-name and/or file-name
* today's date (the day of printing)
* page of pages (ie page 1 of 50 or whatever)
I'm not sure if Calc allows some pages to be different by using "Sections"
but i get the feeling you are trying to keep it much simpler than that for
now! 
However i am not sure if you can include the types of formulas you use
inside the spreadsheet. It might be possible but i have never had any
reason to try.
Once you try a "Print Preview" you might notice that page-sized areas of
the spreadsheet are surrounded by "marching ants" or a slightly
darker/thicker lines or (if you are lucky) by different colour lines. This
can help you work out the divisions between pages. I often change the
width of quite a lot of the cells (usually entire columns but sometimes
rows too) to help with layout.
On a side-issue = Sorry about messages going off-list!
This mailing list is set-up to make it trickier to keep replies on-list so
messages often go off-list quit by accident. Unlike most mailing lists we
have to keep remembering to use "Reply to all" or "Group reply" or "Reply
to list", depending on whixh option our different emailers give. Almost
all have the "Reply to all" but some have more elegant options in addition
to that. So, sorry the previous reply went off-list.
Another peculiarity about this mailing list is that a lot of people use
bottom-posting rather than the top-posting so prevalent in the world
today. The reason we use both, and interspersed replies is to help prepare
people for other mailing lists. Many mailing lists insist on
bottom-posting despite that being completely unfamiliar to any office
worker i've ever met or dealt with outside of OpenSource projects. In many
ways bottom-posting does make a LOT more sense but sadly almost the entire
world is set-up to top-post these days.
So this mailing list attempts to help ease people into the OpenSource world
by allowing top, bottom and interspersed replies. So if you appear to get
a blank reply with nothing in it then try scrolling down to the bottom.
Actually bottom-posters tend to "trim" replies so scrolling becomes less of
an issue but by doing so they sometimes/often remove context that may have
been crucial to understanding previous posts.
Regards from
Tom
Tom Davies wrote:
However i am not sure if you can include the types of formulas you use
inside the spreadsheet. It might be possible but i have never had any
reason to try.
Hi Tom,
Thanks for the explanation about top and bottom posting and the list's
reply settings.
I've tried putting a formula in the heading but it just gets repeated
literally. I also tried putting a formula in a repeated heading line;
that does get interpreted but has the same value on every page.
So either there's some step I've missed in one of those approaches, or
there's some other approach, or it's not possible. It would be nice to
find somebody who knew which!
Thanks, Dave
To Dave Horwith:
I have lost your original post, and have only your response to Tom Davies.
But, as I remember it, you want a figure for the total number of pages to appear on each page.
If I remember this correctly, then your use of "subtotals" confuses me, but:
I think what you need to do is:
1) In "Page" - "Format", turn - say - headers on.
2) Then go to "Insert" - "Headers&Footers"
3) The 5th icon to the right then puts the page-count into an (invisible, to me, at least) header.
Unlike writer, this insert does not show up until you print or use print-preview.
Since this page-count does not go into a cell, you don't see it on-screen until you print or preview.
mxk
Can you guarantee that each page has the same amount of rows?
I am the editor of a document [the IEEE 754-2008 standard] that was created
around 15 years ago (using OpenOffice), and has had nearly 200 drafts, a
number of editors, and countless edits. It was last changed in 2008, but is
now about to go though a new revision cycle.
I was delighted to find that LibreOffice handled the 2008 .odt file almost
perfectly, with only 7 errors (all were weird spurious empty reference tags,
of unknown provenance, that OpenOffice quietly ignored).
While identifying and removing those from the content.xml, I noticed that
there are hundreds (possibly thousands) of redundant tags. These are
typically in the context: <span whatever>text1</span><span
text2</span> where 'whatever' is identical, and either or both
'text1' or 'text2' may be empty.
It there a tool to clean these up? I could write one myself (I recently
wrote an XML parser) but if one already exists ...
Many thanks -- Mike Cowlishaw
[Apologies if this is a duplicate .. I tried it on askLibo some time ago but
it is still "awaiting moderation".]
Hi,
One possible reason is hard formatting. By adding and removing a style hardcoded empty span tags can appear. However this should not happen when using styles (even for bold and so on).
I read this some time ago. Hope that helps
Hello Mike,
You may want to get in touch with the Document Liberation project, our sister project: http://www.documentliberation.org . They may be interested by your input.
Best,
Charles.
Hi
I don't know of an Extension that might do this but then i have never
looked for one tbh.
It might be smart to test that removing them doesn't affect the whole
document before starting. Create a copy of the whole document, or even
just a copy of the "contents.xml" and then try on a small sample.
I guess you have already tried that though!
Regards from
Tom
Hi
Thanks for that link! :))
It looks like a truly excellent project! It is really good to have such a
professional looking web-page that is worded simply enough for normal
office workers to understand. The nearest equivalent on the OASIS site is
horrendously complicated and waaay beyond the quick understanding of most
of my colleagues.
It looks like this question might be a little off-topic for them but they
might well have the expertise to be able to answer this question quickly
and easily.
Many thanks and regards from
Tom
Hi
I can imagine about 3 ways of doing this.
1. Most finance spreadsheets use tons of worksheets within a
workbook/single-file. One for each week/month/quarter or whatever. How?
Well, near the bottom of your spreadsheet you may notice 3 tabs. These can
be renamed and more added. (The ideal way is to figure out the layout of 1
page/sheet, then right-click on it's tab and do "copy". Then each
page/sheet becomes an improvement on the last until you get it
perfect(ish)). Each tab is known as a "worksheet" but it is still within
the same file as the overall file, which is sometimes called a "workbook".
This way it's easier to predict exactly which cell will be the figure to
"carry forwards". It may also make it easier to have common formulae that
are specific to each page but repeated on each page/sheet.
2. Use "Find & Replace" to get to each formula in turn and then edit each
one "by hand". This could easily get quite tedious and prone to error
through boredom and distractions.
3. Errr, i can't quite remember but it was somehow inspired by Andreas'
question. I'm not sure if it was what he was driving at or some weird
off-shoot that i hadn't fully thought through.
4. Maybe use a 2nd worksheet/tab that uses formulae to read the results of
the correct cells and then gets used (back on the main sheet) to do the
calculations. Again this is something i haven't fully thought through and
might well not work or might not be feasible in your "use-case"
5. Another approach might be to develop a Database to hold the data and
use Calc to generate reports. There might already be a purpose-built
template or even a dedicated program we might be able to suggest.
Does any of those sound like something to aim for? I think 1 or 5 might be
good for the future but maybe the most efficient way to minimise the amount
of time to get from where you currently are might be to go for number 2 or
4. It's not very elegant. Andreas' 3 might well be the winner, without my
weird off-shoot.
Regards from
Tom
I don't think this problem is very well defined!
As has already been hinted at by another responder, the first question is: is the number of items on each page fixed (whether or not the same)? If you want to be able to add entries or expand them - such as would spill items over to following pages - then I think the problem is quite difficult, but it may be easier if, for example, you need only to add material at the end of your data.
And do you really mean a page subtotal, i.e. the total for that page alone? If so, I can't see any point in repeating this on the next page. How, for example, would it be of any help to have the total for page two copied at the top of page three, without any account being taken of the values on page one? Why would page seventeen need to have information just about page sixteen and not the previous fifteen pages? Do you instead perhaps require a running total to appear (as "carried forward") at the bottom of each page and the same value to appear (as "brought forward") at the top of the next?
Brian Barker
Hello Mike,
You may want to get in touch with the Document Liberation
project, our sister project:
http://www.documentliberation.org . They may be interested by
your input.
I'll take a look ... thanks.
Mike
I will second that thought about "affect the whole document". I know from a personal experience that some of these document tags may not look like they are needed, but may cause some "troubles" later in the document if removed or modified.
The first time I saw such an issue was creating a document [HTML based] in one WYSIWYG editor and then editing it in a completely different, more complex, one. "XML" based documents seem to have the same issues, depending on the software used in editing the various revisions of the document. The second was with .odt files, with OOXML based ones as the third file type seen with this same issue[s].
ALWAYS keep a "static" original copy of the file to compare the attempts with the "tag edited" version. If you make a simple "error" in removing a single tag [which I know from experience] you could completely mess up your document's format.
Also, sometimes I find that when you edit an older document, from an earlier file format version, with a package that uses a newer, modified, fix, extended, version of the file format [say ODF, OOXML, or any other file format] there may be some compatibility reasons for adding "extra blank tags" to a document that resolves some unknown formatting issue.
So, that said. . . .
What packages have been used to edit the document in the past - version specifics included. The earlier versions of OOo [whatever number it was when OOo started reading/writing .doc files] did different things to a .doc formated document that looked a little different in LO 4.0.x, when I opened them to do some editing and saving them into both .doc and .odt formats. MS Word edits to a "document.odt" file then edited by LO 4.3.x [through 4.4.x] and then editing by AOO, then Word, then back to LO, can insert a lot of unneeded formatting "tags" in the document. I have seen this with LO to Word to LO to Word and back to LO document editing. I have seen this in .docx and .odt files. For such an "round-robin" editing cycle, I tend to ask for the file in some older format, like .doc, to stop the "padding of blank tags" or any other "extra stuffings" of non-needed formatting.
YES
Tom, I really hope someone has created some add on extension/filter to clean out all the unneeded "blank" tags and other formatting info. I really hated to do it myself. I almost rather save the document to a formatted .txt file extension and redo the "real formatting" over the manual removal of all of the "blank" formatting tags.
Hi, Thanks for the thoughts. Yes I always keep back versions (there were
197 in the first revision of the Standard).
So, that said. . . .
What packages have been used to edit the document in the past
- version specifics included. The earlier versions of OOo
[whatever number it was when OOo started reading/writing .doc
files] did different things to a .doc formated document that
looked a little different in LO 4.0.x, when I opened them to
do some editing and saving them into both .doc and .odt
formats. MS Word edits to a "document.odt" file then edited
by LO 4.3.x [through 4.4.x] and then editing by AOO, then
Word, then back to LO, can insert a lot of unneeded
formatting "tags" in the document. I have seen this with LO
to Word to LO to Word and back to LO document editing. I
have seen this in .docx and .odt files. For such an "round-robin"
editing cycle, I tend to ask for the file in some older
format, like .doc, to stop the "padding of blank tags" or any
other "extra stuffings"
of non-needed formatting.
The document was only ever edited with OpenOffice, probably using all
versions current from 2001 -> 2008. It was never a .doc file, only .odt.
YES
Tom, I really hope someone has created some add on
extension/filter to clean out all the unneeded "blank" tags
and other formatting info. I really hated to do it myself.
I almost rather save the document to a formatted .txt file
extension and redo the "real formatting" over the manual
removal of all of the "blank" formatting tags.
It's an extremely complex document with highly technical formatting, so any
manual editing of the tags (or redoing the formatting) isn't really an
option.
That said .. perhaps exporting it to some other format then reloading it
would preserve content and formatting and omit the redundant tags? Any
suggestions as to which format to try would be welcome (not MSWord, by the
sound of it)!
Mike
Mike Cowlishaw wrote:
... (I recently
wrote an XML parser) but if one already exists ...
Somewhat off-topic, but unless you have some exceptionally unique
requirement, I would recommend using one of the existing XML parser
toolkits if possible, as they already 'know' most of the weirdness.
Better to improve a shared resource if necessary rather than create yet
another one.
Cheers, Dave
Can you guarantee that each page has the same amount of rows?
If necessary, yes. In fact it will probably happen without any work on
my part.Cheers, Dave
This may be worth the effort for many pages:
http://www.mediafire.com/view/1045sqauccjloug/SubTotal_PrintRanges.ods
Data in B. Notice that the amount of cells is the same but not
necessarily the amount of values in B.
Columns A to E are set up as print ranges with row #1 as repeating row.
Helper columns F and G are outside the print range. The regular sequence
in F assumes a page break after some amount of rows as specified in J1.
Setup values are J1 and J2. J1 holds the general row count below the
first page. The first page may be different and J2 specifies the start
row (last row of page #1 actually).
The start values in C and E of the start row refer to the running total.
The subsequent rows have identical formulas.
The very last page total in C1001 needs manual correction.
Finally I added a set of user defined Basic functions and applied one of
them in column N. It detects if there is a horizontal page break, so
this formula does not depend on regular cell count on each page. It does
not refer to J1 nor J2.
Column O uses column N to calculate the same sequence as in the regular
sequence of column F. If you replace the formulas in F with the formulas
in O, the row count of the pages should not matter anymore.
One glitch: These user defined functions require Ctrl+Shift+F9 (enforced
recalculation) in order to recalculate and they need some seconds to
recalculate.
Sorry, I had to change the file URL to: