difference between Excel and Calc

users
#1

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

#2

=LEFT(CELL("FileName");8)="'file://"
returns TRUE in Calc and FALSE in Excel

#3

Here's a thought: perhaps you don't need to. How are you intending to use the file name?

o If it is just for human perusal, either in the spreadsheet editing screen or printed output, it may not matter that the formats vary slightly. Humans are good at coping with such variations.

o If you need the file name or some part of it in a precise form for further processing in the spreadsheet, you may well already be editing it with formulae into the form you require. In that case, it should be fairly straightforward to construct a single formula that will extract the exact form you require regardless of which format is delivered by the CELL() function. There is always the IF() function ...

I trust this helps.

Brian Barker

#4

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. :slight_smile:

#5

Hi :slight_smile:
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 :slight_smile:

#6

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.

#7

Yes there are two Tom

Menu/Tools/Options/LibreOffice calc/Formula - Formula Options - Formula
syntax.

and another one in

Menu/Tools/Options/LibreOffice calc/Formula - Detailed calculation settings
- Custom - Details - Reference syntax.

Miguel Ángel.