ERROR 522 despite formula is correct

Hello, I am really stuck with this problem.
Apparently, it only occurs on this cell as if I apply the same formula on a different cell it returns the correct result as also shown in the formula editor.
It should not happen, there are no circular values in the two fields… any idea why?
Thanks in advance :slight_smile:

From your screenshot it is impossible to analyze why there is a circular reference.

I’m seeing the same issue on Calc 7.3.3.2. Happens when I open my spreadsheet, Recalculate Hard (Shift+Ctrl+F9) resolves the issue. (FYI: On Ubuntu 18.04 the desktop appears to be intercepting F9 so only the menubar UI works.)

It is 100% repeatable.

I saw recently somewhere on the internet a suggestion that turning off multi-threaded calculation resolves this issue. It does not resolve the issue for me.

There is no circular reference. A) I’m a programmer. B) I used Trace Precendent and it doesn’t indicate any circular references, only the existing linear ones. C) This sheet is a template, one of 7 identical sheet in the workbook, and only the last one (on the tab bar, but is also the most recently created) has this issue. If it was truly circular, they’d all have it. D) Recalculate can’t resolve a circular reference, so that wouldn’t work if it was real (iterative calculation is off).

It’s pretty clear to me that, on open, there is a recalculation cycle that just craps out part way through.

Which raises another question: these values were already calculated before being saved, why are they being recalculated on open? I took pains to avoid volatile functions.

It was ever saved as .xls(x)?

It was not. I build this file from scratch every time I iterate it. I have an external text file that contains the Basic code I use… so:

  1. File → New → Spreadsheet
  2. Tools → Macros → Edit Macros
  3. Insert a new Module1
  4. Paste in Basic code
  5. Run CreateTemplate() macro to make a new sheet
  6. Copy in data by hand, or sometimes, cut n paste from previous iteration of spreadsheet.
  7. Goto 5.

Please upload an abbreviated anonymized file in which the problem is present here. Thank you.

FTR, @studog1 filed tdf#149146, which “I can’t give a copy of the file that has the problem” makes me want to cry :cry:

I’m a programmer. I’m crying too.

I’ll try anonymizing the data at some point, but it’s a fair bit and there’s no guarantee that the problem will remain unaffected.

Help Error Codes in LibreOffice Calc, Err:522 means “Circular reference”, “Formula refers directly or indirectly to itself”. Either you input the formula in cell Q20 or N30, or one of those cells references the cell where you input the formula.

Thanks, but nope… the formula is on A or B, it’s very far.
I believe it is a bug.
I have done the same formula on a different cell, where it worked and copied it, then I deleted the field where I wanted it to be, and pasted only the formula, it now works… I have no idea what’s happening…
If it was a real error 522 I shouldn’t see the result