Convert formula with STYLE() function to Excel

Discuss the spreadsheet application
Post Reply
ba301
Posts: 5
Joined: Wed Jun 09, 2021 6:57 pm

Convert formula with STYLE() function to Excel

Post by ba301 »

Hello, Im having a tough time figuring out how to convert some function syntax to excel. I use AOO as my platform to create spreadsheets and then convert them to excel to export them to a work computer for a small business that uses excel instead of AOO.

I need to convert 4 different formulae, but all are very similar and all are giving the same error in excel. This is one:

Code: Select all

=IF(SUM(J5:J64)=1;"Behind"&T(STYLE("yellowhl"));""&T(STYLE("default")))
And it converts to excel and gives a #NAME? error. I have very little knowledge of how excels syntax differs from AOO, and have been having little luck at all trying to figure out what to do to get this to convert properly. Excel help only says its a syntax error, and apparently will accept if STYLE is changed to STYLEREF and not give you an invalid function pop up, but nothing changes with the function itself; it still says #NAME?, and I dont actually know if STYLEREF is the equivalent to STYLE or not.

Any help would be appreciated!
OpenOffice 4.1.7 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Help converting AOO syntax to Excel

Post by Zizi64 »

A Feature Comparison between the LibreOffice Calc 7.1 and the MS Excel 2019 / 365

https://wiki.documentfoundation.org/Fea ... soft_Excel


Unfortulately the Apache OpenOffice Calc has not some functions, what the LibreOffice Calc has.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Help converting AOO syntax to Excel

Post by RusselB »

You could save yourself a lot of time and trouble by finding out what version of Excel they are using.
If it is 2000 or later (if memory serves), then you could just save the file(s) is Open Document Format (.ods for spreadsheets) and Excel will open the files.
The the only time you'd need to worry is if Excel didn't have an equivalent function, irrelevant as to the name.
Or, imo better yet, convince them to switch to Open/LibreOffice from Calc (maybe point out that they'd be saving money, as Open/LibreOffice is free, and Excel is not).
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Help converting AOO syntax to Excel

Post by Villeroy »

Excel has stlyes (which nobody uses) but it does not have any STYLE function. Conditional formatting should work though.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
ba301
Posts: 5
Joined: Wed Jun 09, 2021 6:57 pm

Re: Help converting AOO syntax to Excel

Post by ba301 »

Villeroy wrote:Excel has stlyes (which nobody uses) but it does not have any STYLE function. Conditional formatting should work though.
I was afraid of that. I wasnt finding anything on websearches which made me think there wasnt a comparable function.
RusselB wrote:You could save yourself a lot of time and trouble by finding out what version of Excel they are using.
If it is 2000 or later (if memory serves), then you could just save the file(s) is Open Document Format (.ods for spreadsheets) and Excel will open the files.
The the only time you'd need to worry is if Excel didn't have an equivalent function, irrelevant as to the name.
Unfortunately opening the file as ODS produces the exact same problem. But its good to know excel will open ODS files.
RusselB wrote:Or, imo better yet, convince them to switch to Open/LibreOffice from Calc (maybe point out that they'd be saving money, as Open/LibreOffice is free, and Excel is not).
I think thats the best bet at this moment. No one uses excel for any feature other than very basic text based cell edits (no formulae, reference links, etc) except me and I prefer AOO anyway.

Thanks for the advice all.
OpenOffice 4.1.7 on Windows 10
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Help converting AOO syntax to Excel

Post by John_Ha »

Would an alternative be
  • save your AOO or LO development work as a .odt file
  • open the .odt file with MS Excel
  • save the file with Excel as a .xls file
Excel should now do all the conversions which Excel supports and you stick with .xls files with which people are familiar.

You would have to check Excel did not silently drop something Excel does not support.

If you do change, go to LO as AOO is walking dead.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
ba301
Posts: 5
Joined: Wed Jun 09, 2021 6:57 pm

Re: Help converting AOO syntax to Excel

Post by ba301 »

I will try that suggestion, John_Ha.

Do you mean LO is just better than AOO? I have it but have not used it. But I am getting pretty freaking sick of AOO of its own accord deleting cell groupings (hundreds of them) on entire sheets seemingly randomly, and crashing on "bad allocation" whenever I do any heavy (or even minor) reference link work.
OpenOffice 4.1.7 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Help converting AOO syntax to Excel

Post by Villeroy »

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Help converting AOO syntax to Excel

Post by John_Ha »

ba301 wrote:Do you mean LO is just better than AOO?
Yes. LO has more function, better interfaces to MS function and to MS file formats and is under active development. Stick to the stable, older version and even so, expect annoying bugs. LO release testing doesn't seem to be as good as it could be.
ba301 wrote:... crashing on "bad allocation" whenever I do any heavy (or even minor) reference link work.
IIRC, that is a known AOO bug when working with large files. Search on bad allocation error
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
Post Reply