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.

1 Answer

I've just tested the attached file: indirect.ods with current master, as well as with versions 5.3.0.3, 5.2.5.1, and 5.1.6.2. It just works.

You didn't specify your version, so it's hard to check if that's a specific version's failure.

Also, you didn't share a test file to see if there's some difficult-to-spot formula/data error.

EDIT: When opening the XLS(X) files, Calc sets compatibility options, which affect this. You may see this if you open the linked files from your comment below, and go to Tools->Options->LibreOffice Calc->Formula->Detailed Calculation Settings->Custom->Details... and see that Reference syntax for string reference is Excel A1. The Excel syntax is $Location!B5, while LibreOffice's own syntax is $Location.B5. So, you may either make changes here to Use formula syntax (don't forget to set Apply those settings to current document only to avoid changing defaults that affect other files, but I'm afraid this won't save to XLS(X)), or return to previous options page and change Formula Options->Formula syntax to Excel A1 (global setting).

However I agree with @karolus' comment to your question, and you may want to change your formula instead, to avoid using incompatible and error-prone string manipulations: e.g., put =VLOOKUP($J9,$Locations.$B$5:$D$7,2) in K9.

Comments

sorry about that. It is version 5.2.5.1 x64 And the XCEL is 2013 from Office Pro 2013.

I'd upload a file but can't figure out how to do that now other than to post it as an answer (I do see below Remember that you can always revise your original question but don't see how to edit the original post). And it looks like I can't upload in a comment.

Found a work around though. While working on a simple file to upload, I inadvertently saved as xls and not the original xlsx. The simple xls works but not the simple xlsx. Also, opening up your file in EXCEL works except for cell A1 which shows a #REF

sorry to be so dense here but I still don't see how I can upload a file now. Don't see any 'edit' for the original post. Where there are things to do I see
flag offensive close delete
and there is no upload here in comments that is apparent. Does it make sense to delete this and repost?

Attached are three links. IndirectTest-1.xlsx is an original simple sheet showing the ref problem when opened in CALC. IndirectTest-2.xls is the first file opened in EXCEL and saved in xls format. The links work. IndirectTest-3.xlsx is the 2nd file saved back in to xlsx format. Again has the #REF. All 3 work in EXCEL.

Had a bit of trouble getting it to work on the real spreadsheet. I needed the sorted parameter. Until I realized that, I was getting N/A and other problems as the real spreadsheet has blank rows and is not sorted anyway. But it seems to be working fine now.