Problem in Compatibility Microsoft Exel and Openoffice Calc

[belloriccoefamoso]

Hallo all
I'm Enrico, man of 39 from Udine-Italy.
First of all sorry for my bad english i hope i will explain me well anyway.
I have a little network in my office with 4 pc's: one server ubuntu 8.04 with samba to share my files, then other computers that have necessary bot OS Microsoft win2000 and ubuntu 8.04 desktop.
The problem is this:
i have some Exel files where inside are some easy formulas to convert from decimal to HEX.
if i open these files with Exel, i have no problems...
when i open with openoffice calc (ver 2.4) in the cells with these formula i read something like #NOME!?
and if (for wrong) i save the files with Openoffice Calc then with Excel in the cells i read errors
seem like there is an incompatibility in read these formulas from excel and calc and reverse
(evenif i save even the file in EXCEL 2000 .xls format).
To offer mor info in EXCEL the formula is like this "=Dec2Hex(A1-104)"
and if i read with Openoffice Calc i read this in the cell: "#NOME?" and not the hex result i need
and if i click on the cell i read: "=#NOME!Dec2Hex(A1-104)"

Someone can tell me where i wrong?
There is a solution for this problem?
(at the end from pc with EXCEL i only can read the files and if i have to modify i need move on win2000 machine).
The problem is that i need read,edit,save from both different systems.

thanks in advantage for any reply
Enrico

[Dave]

This is not really a forum for comparing the two spreadsheets. No two spreadsheets are alike, so there will always be incompatibility ...both ways. There will also be some compatibility, as in the example you gave:

Your formula =Dec2Hex(A1-104) works just fine, so far as I can see, using version 2.3 in English for Windows. The brackets are done first, so if A1 contained 130, then B1, holding this formula, would show 1A

Perhaps there is a language difference in the syntax? I don't know if that would be the case here. You should check the Help file for the DEC2HEX function.

David.

[belloriccoefamoso]

this formula is written in Windows Office 2000 Excel and of course work fine
BUT in calc is not readed properly
and i can't use both calc and excel
maybe is normal maybe no
i don't know and i ask someone here if have a solution to my problem
thanks

[Hagar Delest]

It seems the translation has not been recognized. Try to install an English language pack, change the user interface language and try again: Change language for user interface (menus & help files).

[belloriccoefamoso]

i have the problem when i try open the files with Calc.
i changed language easy because i had installed not only italian but even english
so i switch it easy
i tried but nothing changed
i will try tomorrow at work where i have the 2 different OS working on machines
because today i'm at home and have only laptop with ubuntu
thanks for now
enrico

[Hagar Delest]

Can you upload the file here?

NB: with my French UI, the function is DECHEX() and is recognized when I use the English name in the English UI (I tried with your very formula on my W2k system).

[belloriccoefamoso]

tomorrow i ill put the file here after check with changing language
anyway i will have to delete some data
the files are about keys of customers...

i'm a locksmith...

enrico

[belloriccoefamoso]

here the 2 files
i had try to change the language in English (all the setting of the Language sections) English UK i tried
but nothing changed

in file 1 (file ok) you can see tat the formulas from my excel are recorgnised

after i saved the file with calc the result is in other file file2...
i hope someone can help me

file1.xls

file ok

(83 KiB) Downloaded 267 times

[belloriccoefamoso]

here the file 2....

(the one don't work)

[Hagar Delest]

Problem is that the first file points to a MS Excel 'plugin' (or something like that): 'C:\Program Files\MsOffice\OFFICE11\MACROLIB\Analysis\ATPVBAEN.XLA' to use the DEC2HEX() function.

Moreover, the numbers have been typed as text. For that, see here: [Solved] Problem with converting text to number. NB: I had to apply the default formatting first (hit CTRL+SHIFT+Space bar) on the selection.

Once you get the numbers as numbers instead of text and that you rewrite the formula, it works fine (function seems to be case sensitive here, so write DEC2HEX instead of Dec2Hex).

Thanks to add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.

[Villeroy]

DEC2HEX (German:DEZINHEX) extends the standard set of spreadsheet functions. In Calc it is part of the analysis add-in. In Excel it may be also part of some add-in.
The internal name as it is stored in the document is com.sun.star.sheet.addin.Analysis.getDec2Hex
The localized display name depends on the user interface language. I suspect that Calc does not translate the internal Excel name of that function which certainly does not start with com.sun.star.... I think, that Calc should be able to map the function names of some standard add-ins. My favourite spreadsheet "Gnumeric" can do this trick, but the problem remains for hundreds of other add-ins providing thousands of additional functions to Excel. Other spreadsheets can not know them even if they have an equivalent function.

OK, having a German OOo I can call a macro to show English formulas.
It pops up an input box with =#NAME!Dec2Hex(A21-104). After removing the #NAME! part, =Dec2Hex(A21-104) is accepted as an English function and my formula bar shows the German version =DEZINHEX(A21-104).
Well, the macro does not help much. At least it can do the translation for you after removing #NAME! taking the remaining name as English input.

Edit: Hagar is right. The text values in column A prevent all calculation in regular spreadsheets. Instead of converting them in place you may convert them in your formula: =Dec2Hex(VALUE(A21)-104)

[belloriccoefamoso]

i had try all
select cell as number, generic, text
i solve the problem only if in CALC i change the formula
in DEC2HEX instead Dec2Hex
but i can't do it manually anytime and can't ask to launch a macro to someone have difficulties to switch computer on

EXCEL formula result
Dec2Hex(A23-104) 0 (zero) result is ok
DEC2HEX(A23-104) #NOME?

CALC =#NAME!Dec2Hex(A23-104) #MACRO?
DEC2HEX(A23-104) ED result ok

the problem is that they don't see the formula of each other......... and i don't find a solution......

[Villeroy]

Your sheet suffers from several problems, which makes it unusable for any other spreadsheet except for Excel.
Select the cells in A and B, change the number format-code from "@" to "Standard".
Find/Replace:
[More Options]
[X] Current selection only
[X] Regular Expressions
Search: .+
Replace: &
OK, this will convert all existing numeric strings to numbers. You won't see anything because the sheet has been formatted to death, but it works. This conversion has to be done once. It will keep compatibility with Excel and all other spreadsheets able to read xls.

Then search the same selection with the same options for:
=#NAME!Dec2Hex
and replace with:
=DEC2HEX
The blue name is the English function name, in a German interface I would use DEZINHEX, your italian interface may require DECINHEX or DECAHEX (I don't speak Italian, see help).

The add-in function name issue has been fixed for version 3.0 http://www.openoffice.org/issues/show_bug.cgi?id=57772

[belloriccoefamoso]

thanks for your explanation but maybe i'm little ignorant in excel...or i don't know
but i din't undersand nothing about yoru explanation

i have to tell u that the errors i see are done only when i try to open with CALC or when i try make something to have a good result

i start from a file that in exec it work very good
no problems from it
the problem is when i try to open by calc and if i save in calc than is unusable from excel

so u talk about a @ and + (???????????????) what ar eu talking abut
can u explain me step by step what to do?
(even my english is not good and is hard for me understand u well)
thanks

[belloriccoefamoso]

i had read many times yoru reply and maybe i had understand a little
i will make a try and will post the resul
thanks for now
enrico

[Villeroy]

"@" stands for number format "Text" applied to column A and B. See Menu:Format>Cells...(Ctrl+1). Ask the person who created the file why he obfuscates numeric values in column A as text and why he makes the formulas in column B uneditable. There are particular reasons to apply number format "@", but not in that context.
That spreadsheet is broken and Excel is the only application, able to calculate anything from text values (nobody, except silly persons consider this as a feature). The problem of unrecognized add-in functions will be resolved soon. Meanwhile a quick and simple search&replace can fix it.

[belloriccoefamoso]

friend
i had created the file
long time ago!
for me is important that i can use it regulary from both excel anc calc and saved from both software and reopened from both.....

[keme]

friend
i had created the file
long time ago!
for me is important that i can use it regulary from both excel anc calc and saved from both software and reopened from both.....

You should be able to, if you apply the cell reformatting and the two search/replace operations Villeroy suggested above. Note that you will have to do that in Calc, as Excel behaves differently with search (more limited regex syntax), as well as with replace (keeps formatting, IIRC, so no data type conversion will occur).