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).
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?