Optimizing a Calc spreadsheet with 1000s of INDIRECT entries

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 (How can I combine the data from ~2000 calc files into a single csv file? ) 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 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.

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.

Thank you @mariosv . 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.