When using the (ADDRESS) function for referring to a cell in another sheet the Sheet name in the result is seperated from the cell specification by a dot. That should be an exclamation mark. ADRESS(1;1;1;TRUE();"Sheet1") gives "Sheet1.$A$1" and should "Sheet1!$A$1". When subsequently using an INDIRECT function with this ddress result wil result in an error. I would categorise this bug in EDITING.
Hi faja, thanks for reporting. Sorry, but in calc the sheet default separator is the a dot, not an exclamation mark. You can change the option on how to see it in: Menu/Tools/Option/LibreOffice calc/Formula/Formula options - formula syntax.
Created attachment 95193 [details] Sample file with/without R1C1 style. El 05/03/14 22:24, Frans Rijven escribió:> hi mariosv, > > If that is so, why then does INDIRECT("Sheet1.A1") fail and gives > ADDRESS(1,1,1,FALSE(),"Sheet1") the result: Sheet1!R1C1 (with exclamationmark) and does > INDIRECT(ADDRESS(1,1,1,FALSE(),"Sheet1"),0) work? > > Changing the option you mentioned did not have any effect. > > greetings, > > faja Please don't send me directly your answers, here is the place for treat the issue. 1) INDIRECT("Sheet1.A1") Works fine for me. 2) ADDRESS(1,1,1,FALSE(),"Sheet1") the result: Sheet1!R1C1 Is the correct result, you are selecting the R1C1 style with the FALSE() parameter. 3) INDIRECT(ADDRESS(1,1,1,FALSE(),"Sheet1"),0) Similar to 2), the last parameter to 0 in the indirect function select R1C1 style. Inner help: " INDIRECT(Ref; A1) Ref represents a reference to a cell or an area (in text form) for which to return the contents. A1 (optional) - if set to 0, the R1C1 notation is used. If this parameter is absent or set to another value than 0, the A1 notation is used. If you open an Excel spreadsheet that uses indirect addresses calculated from string functions, the sheet addresses will not be translated automatically. For example, the Excel address in INDIRECT("filename!sheetname"&B1) is not converted into the Calc address in INDIRECT("filename.sheetname"&B1). " Please also look for the help of direction.
Created attachment 95196 [details] Screenshot