Add serial number to Calc template

I want to add a serial number to a template in Calc. Right now I am using NOW() but, by it’s very nature, the value changes every time I open the document. I’d like a serial number created in a given cell when the template is opened and then immutably saved when the document is saved.

If you tell us a reason for creating serial numbers (or a fix date-time-stamp) somewone may suggest a solution meeting your needs. (I only noticed this old thread a few minutes ago.)

I think I get what you’re asking for. Like for instance if the template is an invoice template, you want each new invoice made using that template to be numbered with the next sequential invoice number, which can never be altered by the person using the template—is that correct?

@Wildcard: I might only offer a custom function assigned to the event ‘New Document’ and inserting the current date-time-stamp. As the mentioned event will only be raised once in all the document’s lifetime this will - in principle, not in detail- do as wanted, if the system time is not tampered with, and the template only working on one system. To get serial numbers, access to a file remembering the used numbers and generating new ones on demand willl be necessary, of course.

well, it seems this is hard to make, because it will require modification of initial template or using some external storage to keep last number.

I’ve done some macros for that, and they use plain text file to keep counter. Its name is hard-coded within macro, so you’ll have to manually edit it before using.

Here is Spreadsheet with counter increasing on each “Save As” (or just save, if there were no name)

What is used: OpenOffice Basic macroses and Tools → Customize → Events.
There is GETCOUNTER() function to get actual counter value.

Here is macro text:

@LibreGuy I think this could be enhanced using special cell value, or empty cell, to denote unfilled serial no., and by checking and overwriting it on load.
I just proposed availability storing external counter.


I could be wrong but Spreadsheet with counter increasing on each “Save As” is something different than a serial number which is generated only once and then never changes.

A solution might be this. Let’s say the serial number would have to show op in cell A1. First thing you have to do is to make sure this cell is protected (right-mouse click, format cells, cell protection). You have to make sure to also (password) protect the sheet, otherwise you or someone else could overwrite the cell. In the cell itself you place a formula, something like this:


This will generate a serial number consisting of the word “SERIAL” added by a timestamp: SERIAL42070

You can save the document and the serial number will never change again (because its length is greater than 1).

Good luck!

@LibreGuy, does cell self-references work at all?

Yes they do, but iterations must be enabled (it’s somewhere in the options menu).