CODEWITHSUNDEEP
libreofficelibreoffice-calc
Heimdall
Heimdall

Reputation: 237

False Error 522: circular reference

I have a large spreadsheet: 700+ rows, each having references to the previous row. I use reference functions: ROW(), COLUMN() and INDIRECT(), ADDRESS(). (Yes, I have considered fixing values every 50-100 rows to reduce calculation trail.)

Until recently I used OpenOffice.org and it worked fine. LibreOffice, however, when the file is opened, seems to give up after some rows and further calculations become Error 522. Sometimes a change makes it re-calculate it all and errors disappear and doesn't reappear when I undo the change. I have also found out about Ctrl-Shift-F9 (must be re-calculate), which also makes errors disappear.

Even though the file has been saved and re-saved by LibreOffice several times it still reports false Error 522 when I open the file, so it doesn't seem to be compatibility problem.

Is the problem that a very long branched out calculation trail makes the software think it will never get to the initial values and therefore it must be circular? (Which my idea of fixing values would solve.) Or could there be something else I may have missed?

UPDATE

I don't see how INDEX() would help. I want to refer to a cell immediately above or a cell from a row immediately above. Cell d46 could point to d45 or b45 or $a45, and that would work when copying a row, but not when inserting or deleting a row: If you insert a row just above, the references pointing 1 row above would start pointing 2 rows above, so each time I would have to edit the formulae. The row (each row) contains several references to the row just above, so I thought the easiest way would be INDIRECT(ADDRESS(ROW()-1,COLUMN())) for the same column or INDIRECT(ADDRESS(ROW()-1,1)) for column A... Any better solutions?

Upvotes: 1

Views: 1590

Answers (1)

Jim K
Jim K

Reputation: 13820

I do not know the specifics of the problem, but it sounds like it would help to simplify the formulas, as you suggested.

Another possibility is to write macros to handle some of the calculation work. Besides Basic, macros can be written in Java, which you seem to be familiar with. Macros can be called from a spreadsheet function, or called when the document is loaded.

It may also help to use a more powerful tool such as LibreOffice Base with MySQL. Often spreadsheets that need a lot of INDIRECT() and ADDRESS() are really using database-type logic.

Upvotes: 0

Related Questions