indirect to different sheet not working

calc

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

spokey
15 ●1 ●2 ●5

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 ( 2017-02-15 10:47:24 +0200 )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 ( 2017-02-15 22:15:52 +0200 )edit

add a comment

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

Mike Kaganski
1942 ●1 ●13 ●25

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

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

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 ( 2017-02-15 19:23:00 +0200 )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 ( 2017-02-15 19:25:58 +0200 )edit

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

Mike Kaganski ( 2017-02-15 20:06:12 +0200 )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 ( 2017-02-15 22:54:02 +0200 )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 ( 2017-02-16 03:01:11 +0200 )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 ( 2017-02-17 04:22:28 +0200 )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 ( 2017-02-22 22:54:34 +0200 )edit

add a comment

indirect to different sheet not working

Comments

1 Answer

Comments

Question Tools

Stats

Related questions

indirect to different sheet not working edit

Comments

1 Answer

Comments

Question Tools

Stats

Related questions

indirect to different sheet not working