indirect to different sheet not working
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.
smells like useless use of
OFFSET, INDIRECT …
Always good to say what versions of software (LO and OS you're working with, and to strip private info from your spreadsheet and upload it here so that others can take a look at it.