Date: prev next · Thread: first prev next last
2016 Archives by date, by thread · List index

Some of the configurations you are referring to are done via the
regional settings (at least in the version of Excel I use): this means
that your separator may not always be the "!" in Excel (I have seen the
"." as well). When I need to have compatible workbooks (Excel and
Calc), I have a configuration page where there is a named cell (I
usually call it "SEP") that contains the formula (assuming "Sheet1"
Then, when you need to dynamically build an address and extract the
content of the cell, you would use something like:
For the CELL("Filename") function, the only consistent element about it
is the "#" used as separator in the filename. You can place this
formula in a cell on your configuration sheet, and name the cell
Then you can use constructs like this:
Also remember that when the workbook is not saved, Calc returns the
sheet name preceded by a "''#" (such as ""#$Sheet1), whereas Excel
returns a blank.
I hope this helps.
Rémy Gauthier.
Le mardi 26 janvier 2016 à 23:32 +0000, Tom Davies a écrit :
Hi :)
Isn't there a setting to change the way cells are addressed in Calc?
Something like;
Tools - Options - Calc
errr, somewhere in there?  I think you have to be in Calc to get
Regards from
Tom :)

On Tuesday, 26 January 2016, Ian McCarthy <> wrote:

On 1/26/2016 1:17 PM, Andreas Säger wrote:

Am 26.01.2016 um 13:53 schrieb Ian McCarthy:

Hi I'm porting a spread sheet from Excel to LibreOffice Calc
and I've
fallen over an incompatibility
as spread sheet will be used by both tools is there a switch I
can use.
  The problem
=CELL("filename",A1) in LO returns
'file:///C:/Users/Ian Mac/Desktop/Accounts15-16.xls'#$Sep 15

where as in Excel the returned data is different
  cant easily paste the excel result here but the terminal
filename is in
square brackets
is there a way I can either spot which program I'm using and
switch the detection of the sheet name

using  LibreOffice

returns TRUE in Calc and FALSE in Excel

Just fallen down another Excel/Calc wormhole - indirect addresses
are different on Excel its 'sheetFred'!$C:$C
 where are its 'sheetFred'.$C:$C in Calc fortunatley the above test
for this just as well. :-)

Cheers Ian Mac - Ian D McCarthy
  Cronkshaw Fold
 Off Alden Rd                    Tel     +44 (0) 1706 218614
 Helmshore                       Fax     +44 (0) 1706 215143
 Rossendale BB4 4AQ              Mobile  +44 (0) 7976 773843
 United Kingdom                  Email

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.