"invalid increment" message when trying to enable regular expressions in libreoffice calc

[gdgilda]

I have an existing spreadsheet that I’ve been using about once a year to help run an auction. The spreadsheet uses regular expressions in the “match” function. When I opened the spreadsheet to prepare for this year’s auction, I discovered that the regular expressions were no longer generating matches. I then noticed under tools->options->libreoffice calc->calculate, there was a checkmark that wasn’t checked to “enable regular expressions in formula”. I tried checking that box and hitting OK, but calc presented a dialog box stating “invalid increment”, and I was forced to cancel out of the change.

Are there any ideas on how to get rid of the “invalid increment” message so that I can enable regular expressions in my spreadsheet?

I’m running libreoffice 4.2.7.2 build ID 420m0(Build:2) on a standard ubuntu 14.04 LTS installation. If I open this same spreadsheet on a RH6 box running openoffice 4.x, the spreadsheet opens fine and all of the formulas work.

Glenn

[karolus]

We need - minimal the problematic Formula - probably the whole Spreadsheet (without personal data) - to provide a working solution.

[Lupp]

Such strange errors never reported before are sometimes due to user profile corruption. I would suggest you thoroughly and completely study this guide - and be careful then resetting the profile.

[gdgilda]

Thanks for the tip on the user profile. I read the guide and renamed my ~/.config/libreoffice/4/user directory after closing the spreadsheet. Unfortunately, upon re-opening the spreadsheet, the problem persisted, i.e. I can’t enable regular expressions in calc.

On the “Items” tab, look at column CD to see the match function with the regular expression. I’m simply trying to match on a number and return the index of that column to identify a bidder number. If I could enable regular expressions, I believe this would work (as mentioned before, it works fine in openoffice). I’d attach she spreadsheet but don’t have 3 points yet. Guess I need to figure out how to do that… in the meantime, the cell
formula looks something like this: =MATCH("[0-9]+",G35:CC35)
auction debug.ods

Last year I was running ubuntu 12.04 LTS, guessing I was on libreoffice 3 at that time? As I recall, this was working then.

[karolus]

You should now have enough karma to upload…

[gdgilda]

Thanks for adding the karma… I editted my previous post to add the file (auction debug.ods). See items sheet, column CD for the regular expression that doesn’t yield what I’m expecting, i.e. the index of a column with a number in it.

[mariosv]

I think is in relation with the option in the same place:

Menu/Tools/Options/LibreOffice Calc/Formula - Iterative references

If the Minimun change is zero gives this error message.

Edited 201503302331

Without the third parameter regular expression doesn’t work.

CD2: =MATCH("[0-9]+";G2:CC2; 0 )

LibreOffice help
MATCH
Returns the relative position of an item in an array that matches a specified value. The function returns the position of the value found in the lookup_array as a number.
Syntax
MATCH(SearchCriterion; LookupArray; Type)
SearchCriterion is the value which is to be searched for in the single-row or single-column array.
LookupArray is the reference searched. A lookup array can be a single row or column, or part of a single row or column.
Type may take the values 1, 0, or -1. If Type = 1 or if this optional parameter is missing, it is assumed that the first column of the search array is sorted in ascending order. If Type = -1 it is assumed that the column in sorted in descending order. This corresponds to the same function in Microsoft Excel.
If Type = 0, only exact matches are found. If the search criterion is found more than once, the function returns the index of the first matching value.
Only if Type = 0 can you search for regular expressions.
If Type = 1 or the third parameter is missing, the index of the last value that is smaller or equal to the search criterion is returned. This applies even when the search array is not sorted. For Type = -1, the first value that is larger or equal is returned.
The search supports regular expressions. You can enter “all.*”, for example to find the first location of “all” followed by any characters. If you want to search for a text that is also a regular expression, you must precede every character with a \ character. You can switch the automatic evaluation of regular expression on and off in Tools - Options - LibreOffice Calc - Calculate.

[karolus]

Hallo

Start in Items.CD2 with formula:

=IFERROR(MATCH("\d+";G2:CC2;0);"")

start in Items2Bidders.E2 with formula:

=IFERROR(MATCH("\d+";Items.G2:CC2;0);"")

pull both formulas down.

Maybe you also want to capsulate the VLOOKUP(..) in Column D into :

=IFERROR(VLOOKUP(E2;Bidders.A$2:B$51;2);"")