Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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.