Ask Your Question
1

indirect to different sheet not working

asked 2017-02-15 04:57:06 +0100

spokey gravatar image

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.

edit retag flag offensive close merge delete

Comments

smells like useless use of OFFSET, INDIRECT …

karolus gravatar imagekarolus ( 2017-02-15 10:47:24 +0100 )edit

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.

EasyTrieve gravatar imageEasyTrieve ( 2017-02-15 22:15:52 +0100 )edit

1 answer

Sort by » oldest newest most voted
1

answered 2017-02-15 07:08:49 +0100

Mike Kaganski gravatar image

updated 2017-02-17 05:57:15 +0100

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.

edit flag offensive delete link more

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.

spokey gravatar imagespokey ( 2017-02-15 19:23:00 +0100 )edit

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

spokey gravatar imagespokey ( 2017-02-15 19:25:58 +0100 )edit

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

Mike Kaganski gravatar imageMike Kaganski ( 2017-02-15 20:06:12 +0100 )edit

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?

spokey gravatar imagespokey ( 2017-02-15 22:54:02 +0100 )edit

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

Mike Kaganski gravatar imageMike Kaganski ( 2017-02-16 03:01:11 +0100 )edit

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 gravatar imagespokey ( 2017-02-17 04:22:28 +0100 )edit

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.

spokey gravatar imagespokey ( 2017-02-22 22:54:34 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-02-15 04:57:06 +0100

Seen: 48 times

Last updated: Feb 17