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
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.
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.
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))
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!
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!
Created attachment 100039 [details] Test file to illustrate the bug This is a .XLS Excel spread sheet file
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.
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.
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.