Indirect to different sheet not working

[spokey]

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.

[karolus]

smells like useless use of OFFSET, INDIRECT …

[EasyTrieve]

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.

[mikekaganski]

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.

[spokey]

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.

[spokey]

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

[mikekaganski]

I suppose you should be able to upload file since I upped your karma

[spokey]

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?

[mikekaganski]

I suppose it makes sense to share a file somewhere and post a link here in a comment, if edit is unavailable.

[spokey]

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.

[spokey]

Thanks

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.

[dan325]

The problem is that Excel uses ‘!’ where Calc uses ‘.’

So – if you swapped out “=$Locations.B5” for “=Locations!B5”, it would work in Excel. (Excel also doesn’t like the preceeding ‘$’, but you can ditch that and both programs will be fine.

I figured out a workaround, though. It’s an ugly hack, but it does work: use the iferror function:

=IFERROR(libreoffice syntax, excel syntax)

e.g.
=IFERROR(INDIRECT(“Sheet1.A2”),INDIRECT(“Sheet1!A2”))

That works for me.

[erAck]

Calc understands the ! sheet delimiter in INDIRECT(), unless you force it not to under Tools → Options → Calc → Formula → Detailed Calculation Settings → Custom Details → Reference syntax for string reference. That option is set to the right order when importing .xlsx files and didn’t exist in early versions (don’t recall off my head since when). Your construct with IFERROR() is unnecessary and error prone in itself because it also suppresses “real” reference errors.