Ask Your Question
0

Optimizing a Calc spreadsheet with 1000s of INDIRECT entries

asked 2018-07-14 14:22:17 +0200

gtomorrow gravatar image

I have an inventory of over 3000 items, each item has its own spreadsheet listing name, year, company, etc. In each spreadsheet there is also an AVG function for price fluctuation, with a defined array of up to 500 entries and a COUNT function of entries to average. I was looking for a way to create a master spreadsheet compiling all item spreadsheets without having to either copy/paste data from each separate spreadsheet to the master (static data) or linking each cell from over 3000 spreadsheets one by one.

I came across @Lupp 's brilliant solution here (https://ask.libreoffice.org/en/questi... ) and set about adapting the example to my needs.

Unfortunately, once I get over 250 or so entries, LibreOffice starts to bog down, CPU- and RAM-wise. Over 1000 entries and LibreOffice uses 4.7GB of RAM and 100% CPU, taking well over a minute to register changes and/or saving the document. Even scrolling stalls the program. For the technically curious, I'm on an i3 laptop with 6GB RAM running Ubuntu 16.04 LTS, LibreOffice 5.1.6.2. Yes, it's not the latest machine out there but more than sufficient for office-suite work. I can't imagine that the original poster @henryh13 didn't experience the same kind of performance issues.

Can @Lupp or any of the other resident experts here think of a less-CPU/memory-intensive way of corraling thousands of .ods files in a master document? Thanks in advance.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2018-07-15 13:42:33 +0200

m.a.riosv gravatar image

Well INDIRECT() it's a volatile function, so it is recalculate every time you enter something in any cell. having a lot of them I think it's what makes the spreadsheet slow.

Disable the automatic calculation on Menu/Data/Calculate/Autocalculate.

Using [F9] to do the calculation when you like.

edit flag offensive delete link more

Comments

Thank you @m.a.riosv . You've confirmed my suspicions. I'm going to have to either follow your suggestion, create a new master document with minimal INDIRECT calls and more static data or create multiple master documents.

gtomorrow gravatar imagegtomorrow ( 2018-07-16 11:56:25 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-07-14 14:22:17 +0200

Seen: 60 times

Last updated: Jul 15 '18