Bug 62732 - EDITING: INDIRECT function to build access to a cell on another sheet results in #REF! error
Summary: EDITING: INDIRECT function to build access to a cell on another sheet results...
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.6.5.2 release
Hardware: Other Windows (All)
: low enhancement
Assignee: Not Assigned
URL:
Whiteboard: BSA
Keywords:
Depends on:
Blocks:
 
Reported: 2013-03-25 18:00 UTC by Paul Kinzelman
Modified: 2015-01-22 15:43 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Test file to illustrate the bug (26.00 KB, application/vnd.ms-excel)
2014-05-28 15:13 UTC, Paul Kinzelman
Details
Test file with several options. (12.50 KB, application/vnd.ms-excel)
2014-05-28 22:48 UTC, m.a.riosv
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Paul Kinzelman 2013-03-25 18:00:37 UTC
Problem description: 
Indirect function does not seem to work in Calc but does work in Excel

Steps to reproduce:
1. I have the formula =INDIRECT($N$5&"!Q16") in a cell, and $N$5 contains "2012" which is the name of another sheet, and cell 2012!Q16 contains an integer, Calc gives me a "#REF!" error. M$ Excel correctly grabs the number in Q16 on sheet 2012 for further calculation.

Current behavior: #REF! error

Expected behavior: Should result in the contents of the cell

Could the problem be due to confusion whether 2012 is a string or an integer?

              
Operating System: Windows 7
Version: 3.6.5.2 release
Comment 1 m.a.riosv 2013-03-25 23:43:32 UTC
Hi Paul,
the "!" it is not the default for calc, default is "." , if you want use "!" in a formula you need to change the option in Menu/Tools/Options/LibreOffice Calc/Formula.

But INDIRECT() only allow the "!" using the Excel R1C1 syntax. You can see it in the help for INDIRECT().

I think it is not a bug.
Comment 2 Paul Kinzelman 2013-03-26 15:29:02 UTC
Thanks mariosv for the hint. It did take a bit of flailing around, but I was able to get it to work. The trick is to change:

Tools | Option | LibreOffice Calc | Formula | Formula syntax -> Excel A1

I would never have discovered this without your help.

I think it'd be nice to have some kind of 'compatibility' mode that would just set things up (like the Excel A1 option) to get compatibility with Excel.

Because this is so obscure, I'll leave this bug report as-is, and let somebody farther up the food chain close it, so that perhaps somebody might consider adding the Excel compatibility feature.
Comment 3 m.a.riosv 2013-03-26 17:29:54 UTC
Hi Paul,
then please modify the title and changes the importance from normal to enhancement.

Also a trick to get a better compatibility is enter in a cell the separator and reference this cell from indirect() function.
Or I think can work avoiding the text "!" with INDIRECT() referenced to another sheet and change the sheet name with SUBSTITUTE(), e.g. INDIRECT(SUBSTITUTE(CELL("address";othersheetname!q16);"othersheetname";$N$5))
Comment 4 Paul Kinzelman 2013-03-26 17:43:20 UTC
I changed it to 'low' and 'enhancement' as you suggested, but I didn't understand the change you are suggesting to the title - I like to make it very descriptive.

Your other suggestion looks interesting but a lot more complicated so I think I'll just stick with what I've got, but keep a pointer to your suggestions in case I have future problems.

Thanks very much!
Comment 5 Joel Madero 2014-05-28 02:34:38 UTC
Please provide a zip of the two files - much easier for us to triage and confirm if we have test documents that we can see the exact same behavior. Marking as NEEDINFO - once test document is attached mark the bug as UNCONFIRMED and we should be able to confirm the enhancement request quickly.

Thanks!
Comment 6 Paul Kinzelman 2014-05-28 15:13:16 UTC
Created attachment 100039 [details]
Test file to illustrate the bug

This is a .XLS Excel spread sheet file
Comment 7 Paul Kinzelman 2014-05-28 15:20:17 UTC
I've created a short test file (created by and works in Office 2013) to illustrate the bug.

I'm currently using LibreOffice 4.1.5.3

My initial speculation was wrong, none of the 3 cell formats (general, number, and text) are able to work with the INDIRECT function in LibreOffice to point at another sheet, whereas all 3 work in Excel in Office 2013.
Comment 8 m.a.riosv 2014-05-28 22:48:54 UTC
Created attachment 100062 [details]
Test file with several options.

(In reply to comment #2)
....
> Tools | Option | LibreOffice Calc | Formula | Formula syntax -> Excel A1
> 
....

Do you forget to set up this option?.
File works fine with it.

Attached your file with a couple of options to get it working automatically without intervention, with any of the options Calc A1 or Excel A1.

I think there is no bug.

Changed the status to resolved worksforme.
Comment 9 Paul Kinzelman 2014-05-29 00:36:32 UTC
You're right, it's been so long since I posted the note, I forgot the details about it. That must be why I changed the type from bug to enhancement.

When I change the default, then the test file works.

It'd be even nicer if the default were to work with both formats.