Description: I have found that a formula like: =INDIRECTO(DIRECCION(1;1;;;"Specs")) produces a #REF! result. That should be equivalent to: =INDIRECTO(DIRECCION(1;1;;1;"Specs");1) which also fails. However if R1C1 notaition is used, it works fine: =INDIRECTO(DIRECCION(1;1;;0;"Specs");0) which produces the right result. It looks like when the A1 style argument is ommited or set to its default value (non zero) is not applied correctly. Steps to Reproduce: See Description. Actual Results: #REF! result Expected Results: Actual value of referenced cell Reproducible: Always User Profile Reset: No Additional Info: After some testing it seems that ADDRESS function is returning the expected value as described in the help. It looks like INDIRECT function does not work correctly when A1 style is especified explicitly or by default. Spreadsheets using this functions become useless due to #REF! values produced. User-Agent: Mozilla/5.0 (Windows NT 6.1; rv:51.0) Gecko/20100101 Firefox/51.0
Created attachment 131470 [details] Test Address+Indirect There are two places where to set up formula syntax: - Menu/Tools/Options/LibreOffice calc/Formula - Formula Option - Formula syntax. - Menu/Tools/Options/LibreOffice calc/Formula - Detailed Calculation Settings - Custom - Details - Reference syntax for string reference. With both as 'Calc A1' results look ok. Attached a sample file that looks ok for me.
The file in which I noticed the problem is an Microsoft Excel file with extension XLSX. I have tried to create a simple example file created from scratch with LibreOffice Calc and it seems to work just fine. Right now I do not have accessto Microsoft Office to try to create a simple example with it, so I will try to create a simplified version of the file I observed this behaviour with.
Created attachment 131471 [details] Check cell C2 in sheet Hoja2 Please check C2 in sheet Hoja2
The issue is with the option in: Menu/Tools/Options/LibreOffice calc/Formula - Detailed Calculation Settings - Custom - Details - Reference syntax for string reference. It is with your file 'Excel A1', so C2 having 'Calc A1' reference gives an error, change it to 'Calc A1 | Excel A1" and all formulas work fine.
thanks a lot!! it was driving me crazy I really appreciate your help, I didn't know there were so many options with such an impact on results
There was a great effort from devs to achieve compatibility ways for INDIRECT(). The better compatibility it's avoiding INDIRECT() whenever it's possible, besides it is a volatile function, used massively can slow down the spreadsheet.