I don't have a lot of experience with Libre Office and have a problem with a sheet that works in EXCEL but not in Calc.
I have one xls file with several sheets. In sheet1 named Locations, I have three columns, a name, 1 way distance, and round trip distance. I use this to keep track of medical expense mileage.
In sheet2 I use indirect and offset to reference the info in sheet1 such that I can reference just the name in sheet 2 and it displays miles and computes the mileage deduction. As I said , this all works in excel but some of the cells in sheet 2 give a reference error.
For example, cell J9 contains "=$Locations.B5" works, fine. Displays the Location text.
but J10 does not. =IF(J9="", "", OFFSET(INDIRECT(MID(FORMULA(J9),2,99)),0,1)) produces a #REF error
MID(FORMULA(J9),2,99) correctly gives me "$Location.B5"
But the indirect produces a #REF error.