How do I make the fill series increment up instead of down?

I tried using bug report but get html errors.
I use LibreOffice at work and at home.
Work computer is a Windows 7 based laptop with LibreOffice installed.
At home I use Ubuntu 12.04 with the latest LibreOffice installed.

On both, I have found an issue with trying to increment codes, such as plat numbers, down by using the fill series with a mouse. On regular numbers, things increment correctly. But, on codes, such as XX105-0110, the incrementation is down, not up. So, you get XX105-0109, XX105-0108, etc, instead of the expected, XX105-0111, XX105-0112, etc. This is extremely frustrating since it is all we do at work with spreadsheets. Tract numbers and plats are what we compile and manually entering 200 tracts, one by one, on the sheet is frustrating. Especially since I can use Microshaft Excel and it works just as expected. Any way to remedy this? Why have this so different from Excel in this one area? I am trying to convince everyone here at work to use LibreOffice, but with that huge issue, it is a tough sell.

===== I put this last part of my answer on top because it might be what visitors are looking for. =====

Coming back in the “final end” with the “true solution”:

As so often I was badly informed (and though the only one to answer). As a side-effect of taking part in another forum I learned from “MrProgrammer” and from “atzi” that Calc (AOO and LibreO as well) since long was trimmed to be smart enough for the task. You simply have to implicitly describe the task by giving 2 consecutive cells and selecting them both before filling down by dragging the handle. Try it with.


getting next:


You may even define a different increment this way:


e.g. with an increment of 5 (or -5 as Calc will still interpret it):


There will, of course, come up other cases where the smartness of this feature won’t satisfy somebody. Please confer this forum thread for your further studies. I got there from this one.

Hope this is still of interest for you.

===== Next coming the originally first part of my answer. =====


The ‘serial’ filling is expected to also work with texts ending in a part that can be recognised as a decimal integer number. If we delimit this part in a “greedy” way, meaning in this case, to include the minus ("-") sign which is necessary for entering negative integers, the counting upward from -0110 will correctly produce -0109 as the next value. (Does MS not know that?)

You don’t like that? Well, it is correct, but there are workarounds if “less correct and more as I’m used to” is intended. For a detailed advice I might need some additional information. In specific: Are you allowed to replace the minus (hyphen) by another kind of a short-line-character, by a CHAR(173), e.g.? This will be treated as a non-numeric character. If this is to your disposition you may also use “#” or “~” or something else. Just not the numeric minus sign. You should realise that spreadsheet software comes from the engineering field and is still largely used there.

Please, don’t judge from a short visit. Your judgement as an experienced user of Excel might be biased by some MS specifics that aren’t simply correct at all. But: If you want a software acting exactly as MS Excel does you will need Excel and nothing else!

Alas! I remember complaints from Excel users over decades now that Excel itself sometimes doesn’t know how Excel works (or saves documents) after the version changed or someone made a decision in the dark.


Coming back in the end (after some pursuit of happiness):

There is attached an example Calc document ask44711IncremeningProblem001.ods which is containing a small engine which may help in a few cases. It’s not satisfactory at all, I think. The document is also containing a few words of comment and a request for telling me how Excel is behaving in a specific situation.

I cannot solve the immanent dilemma. It is actually a case of expecting a specifice software behaviour seen as the one to expect from a specific point of view not having in mind that there may be other specific points of view expecting another specific behaviour. A concept to handle such cases should be to prefer a decision which might minimise conflicts with other preestablished concepts, emphasising logical ones. As far as I understand Calc chose this way. An actual tendency is to guess what most users might suit under some not expressly given limitations. I personally could well live with abandoning a “smart behaviour” in cases where smartness may not be indisputable. That’s not the way things go, however. More likely is, we might get a request for a feature to automatically increase/decrease the middle part of a text containing digits - or the front part. I don’t like to take part in the struggle for he correct endianness.

Not sure why the condescending tone, however, I will answer your questions:
It has nothing to do with liking it or disliking it, nor does it have to do with being a MS fan. I can’t use MS Office at home without severe pains trying to get it to work in WINE.
No, we can not replace the dash symbol with another. This is not allowed by the client. If we are to make a spreadsheet of information for tracts, acreages, it is to be exactly as it shows in the land management system.

Sorry! I didn’t intend to use a “condescending tone”. I just am losing my nerve sometimes if I feel implicitly assumed something correct just because (e.g.) Excel does it that way. My reaction may be similar to that of a sensitised alergic. Besides I’m German and haven’t at my disposition a “Native English” - and I dont know the social habits as they are in the US. Be lenient with me, please.

I will enhance my above answer as soon as I find the time.

It was, but that is ok! I am not a rude person. My question was legitimate for my line of work. I realized that this may not apply to ANYONE else, but I thought the question deserved an audience. Sometimes I have to enter 500+ tracts and each has to be typed one by one in LibreOffice. :frowning: Makes for a nuisance on the fingers and so on, but it is not unbearable. Just looking for way to perhaps alleviate the pain of so much repetitious typing and completely NOT use M$ products! Guten tag!

Needing it for as many as 500+ tracts (I didn’t know the word in the sense applicable here) my little engine will surely work more efficient than entering them one by one - and subsequently verifying “no errors”.

You only have to enter the base string and two numbers (or only one as the increment may always be 1 in your case). The rest is a ‘Copy’ / ‘Paste text only’.

I read through the supplied ods document and am very impressed with the concepts. However, as I was reading, it occurred to me that there may be a MUCH simpler way to resolve said issue. How about utilising a format feature [format cell(s)] There are numerous options, so adding another should not (?) be such a bother and it would be an added feature that LibreOffice can tout over the others. How about formatting a cell to literally ignore the text entered as numerical or whatever and therefore eliminating the program determining that as a minus. So with “VW055-0001” you get it treated as literal text. No mathematical figures, just incrementation of numerical values, if needed. This is something that would have to be added to LibreOffice code, so I do not know if it is feasible, but it is, perhaps, the simplest of solutions to the problem. I want to be succinct. I realise I may be the only one who has an issue with this. Thank you all for reading! Cheers!

Further, It could even be called “null value text” or “M$ text” … :slight_smile:

I seem to misunderstand the above suggestion. In what way should a cell containing a minus sign in its text and formatted to literally ignore the numeric/arithmetic role of it pass this fact to the evaluator working on the formula contained in another cell and polling the mentioned text for that purpose? (Or to a process of incrementing e.g.)

How did Excel act on the circular process I suggested to feed it with? I’m really interested.

I dont know how to upload the file here, so I will try to explain what happened when filling up, copying over and filling down… Basically, it goes from A003 to A011… the copy over then fill down goes from A011 to A025. The only way to make excel actually increment without negating the hyphen (-) you have to select up do the field with the first hyphen and then fill up. Rather weird, but what Microsoft coding isnt? LOL … One important note. Excel refused to open the ods file!

I had to save as an xlsx before Excel would even touch it. My suggestion was to make a format field type that says anything other than numbers are to treated as plain text. No =, -, +, etc. That is treated just as any letter of the alphabet is treated. That may be customizable in LibreOffice already, I dont know. I hope I make more sense now. My apologies if I do not.

The circular process does not close. It ends up overwriting the original value in N14 with “A015” This seems odd, too, you would think it would close the circuit with the same numbers. Very odd. I cant say that it is wrong, but that is what it does! Thanks again for your patience and assistance

  1. Obsolete! (See above comment.)

  2. Seems my understanding of your format suggestion was correct. But isn’t it the incrementing process itself which has to get the text, analyse it,and then decide what text should be the next one. Thus the format must be passed to and interpreted by the routine performing the process.

  3. I stress once more that there is already a tool creating a series while filling. Enhancement for thr controlling dialogue needed? File a feature request.

Enhancing the fill series may be a way to go… like when you hold Ctrl, you get the same result instead of incremented results… perhaps hold shift to get the opposite incrementation than is normal to Calc. That might be a neat little tool for anyone. Being able to increment up or down, depending on the status of the Shift key or whatever key works. Just thoughts. I am not a programmer.