Hi,
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"
exists):
=IF(ISERROR(INDIRECT("Sheet1.A1"));"!";".")
Then, when you need to dynamically build an address and extract the
content of the cell, you would use something like:
=INDIRECT("Sheet_Name"&SEP&"Cell_Address")
or
=INDIRECT(CONCATENATE("Sheet_Name";SEP;"Cell_Address"))
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
APP_IS_EXCEL:
=IF(ISERROR(FIND("#",CELL("Filename"))),"Excel","Calc")="Excel"
Then you can use constructs like this:
=IF(APP_IS_EXCEL;something_for_excel;something_for_calc)
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
> those
> options.
> Regards from
> Tom :)
>
>
>
> On Tuesday, 26 January 2016, Ian McCarthy <i...@cronkshaw.com> 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
> > > > therefore
> > > > switch the detection of the sheet name
> > > >
> > > > using LibreOffice 5.0.4.2
> > > >
> > > > =LEFT(CELL("FileName");8)="'file://"
> > > returns TRUE in Calc and FALSE in Excel
> > >
> > >
> > > Just fallen down another Excel/Calc wormhole - indirect addresses
> > > formats
> > are different on Excel its 'sheetFred'!$C:$C
> > where are its 'sheetFred'.$C:$C in Calc fortunatley the above test
> > works
> > 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 i...@cronkshaw.com
> >
> >
> > --
> > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> > Problems?
> > http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscrib
> > e/
> > Posting guidelines + more:
> > http://wiki.documentfoundation.org/Netiquette
> > List archive: http://listarchives.libreoffice.org/global/users/
> > All messages sent to this list will be publicly archived and cannot
> > be
> > deleted
> >
>
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
