[Calc] validity is slow

English
, ,
#1

Hi,

I noticed my spreadsheet runs very slow.

I was able to detect the problem and it seems to be caused by a data validity setting.

I will try to explain the situation. I have 2 sheets, let’s call them X and Y.

Sheet Y contains “Categories” in the first row, and “Options” in the columns (starting at row 2).

For example:

   A         B
1 color     size
2 red       small
3 green     medium

In the example, color and size are categories and red, green, small and medium are the options.

I created a named range “Categories” for the categories, and a named range “Options” for the options.

Now in sheet X column A, I use the data validity option and I set the range to “Categories” so I can pick a category. This works fine. In the next column in sheet X (column B) I also use the data validity option. Once you have picked a category in column A, you can pick one of its corresponding options in column B. In the data validity section I set this formula as the range:

INDEX(Options;0;MATCH(A1;Categories;0)) // this is the data validity formula for cell B1

This data validity formula is copied to the first 5000 rows (sheet X, column B), and incredibly slows down everything. Saving the document takes about 15 seconds. If I remove the data validity option, it only takes 2 seconds.

Is there a way I can replace this formula with another one that does exactly the same thing?

#2

Maybe the source of the issue.
Please verify that you have activated the option:
Menu/Tools/Options/LibreOffice calc/Calculate - Search creteria= and <> must apply to whole cells.
because there is a bug that makes it very slow with some function that use regular expressions, if it is not activated.
https://bugs.documentfoundation.org/show_bug.cgi?id=79892

#3

Thank you, unfortunately the option is already activated so this is not the problem.

#4

Try to set individual name for each of the options group (“color” for $A$2:$A$3, “size” for $B$2:$B$3, etc.) and use
INDIRECT($A1) in B1

OK. Try this example - BigValList.ods

#5

That is not an option actually, since the options should be automatically detected, without having to add new names eacht time I add a category or option group.

#6

The macro for the event “Content of the sheet Y changed” can do it for you automatically. But the function INDIRECT() is easier in the performance than INDEX(MATCH()) without sorting (IMHO!)

Oh, sorry! I have just read carefully this part of your question - “…formula is copied to the first 5000 rows”. Do you really want to fill 10,000 (!!!) cells directly in a sheet? Perhaps the combination of Calc and Base would facilitate the task?

#7

Imagine this. There are 2 sheets. In the 2nd sheet I create categories like color, size and so on. These categories are on top of each column. Beneath them are their options: red, medium and so on. Now in the first sheet in A1 I want to be aple to pick a category from a drop down box eg size. Now in B1 I want to pick one of the options eg medium. For options and categories I created a named INDIRECT function. To match B1 I use the INDEX formula. Is there an alternative for the INDEX formula?

#8

That looks very nice! (very complicated as well …) I might have found out what is causing the slowness. i am rebuilding the template. If it is what I think, I’ll let you know!

#9

UPDATE:

I rebuild the template and now it works as it should. It’s no longer slow.

I think I found the cause of the document being slow, and I would like your opinion on it. What happened in the first spreadsheet, was that I applied conditional formatting to some columns. In total there were only 2 conditional formatting rules and there was no problem at all. One of these rules (among others) applied to column D. Now somehow, I am not exactly sure when it happened, I might have deleted or inserted a row, or changed the styles in column D. Not exactly sure, but I think something like that happened. So what happened is that (in the slow document) the 2 conditional formatting rules got fragmented and cell D1 to D5000 all got their own conditional formatting rule. So somehow the original formatting rule got broke, which resulted in thousands of new formatting rules! So problably that’s what was slowing my document down.

Now what I would like to know … is it normal that the conditional formatting rules change by themselves? Or is this a bug? Try for yourself: select all cells in a spreadsheet and apply a simple conditional formatting rule (condition) to them, for example, “cell value is equal to 1”, “apply style heading 1”. Now when you go to the “manage” section of the conditional formatting, you can see there is only 1 rule. Now in your spreadsheet select a column and delete it. Then press control+z to undo. Now go back to the manage section again. All of a sudden there a 2 rules now. Is this a bug? Any advice would be very welcome.

#10

There is a meta bug https://bugs.documentfoundation.org/show_bug.cgi?id=87351

#11

Thanks, but isn’t that something different than I described? Did you try what I described?

#12

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.