| Summary: | HYPERLINK improperly converts to XLS and XLSX formats | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | Lew Merrick <tangent> |
| Component: | Calc | Assignee: | Not Assigned <libreoffice-bugs> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | CC: | gekacheka, miguelangelrv |
| Priority: | medium | ||
| Version: | 4.1.3.2 release | ||
| Hardware: | All | ||
| OS: | Windows (All) | ||
| Whiteboard: | |||
| Crash report or crash signature: | Regression By: | ||
| Attachments: | Sample with the hyperlink with functions. | ||
|
Description
Lew Merrick
2013-11-27 19:05:06 UTC
Calc formats an internal reference hyperlink as: =HYPERLINK("#Sheet Name.A1","Comment")
Excel formats an internal reference hyperlink as: =HYPERLINK("[]#Sheet Name.A1","Comment")
I have run this past a friend of mine who is a MS employee on the Office programming team. He "suggests" that a format for the link of "#Sheet Name!A1" will sometimes work.
I have tested this translation against Excel 2003, 2010, and 2012.
Hi Lew, thanks for reporting. Please take a look to the options in: Menu/Tools/Options/LibreOffice calc/Formula - Custom - Details - Reference syntax, there is an option for select Excel types. Interesting, but somewhat disingenuous. I read the entire entry in the Help system about the HYPERLINK function and found NOTHING to guide me to such an area. It seems strange to me that the SAVE AS to Excel format would NOT make such a "parse & correct" action as this should be the DEFAULT for an xls or xlsx format files. The next question that will arise is how much work will need to be done to convert the 70-80 files where the HYPERLINK function is used that need to be provided in xls or xlsx format for sharing? IMO, I must expect that a literal text is not changed in any way. For this is we need to find the proper way with functions using literals as parameter.
I have not excel to verify, please try if a formula like:
=HYPERLINK("#"&CELL("address";SheetName.A1);"Comment")
works in excel, with this form we can avoid text conversions.
Mario -- I do not understand your suggestion. The "CELL" operand does not seem to function for me (though I readily admit that could be my own lack). When I make the Menu/Tools/Options/LibreOffice calc/Formula - Custom - Details - Reference syntax setting, I get nothing that appears to fix my problem. ??? It seems to me that an "export function" should be expected to revise differences between the LibreOffice internal function syntax and that of the system being exported. CELL() is a function, if your locale language is not English, you can change while you are entering the formula, Menu/Tools/LibreOffice calc/Formula/Formula options - Use English function names, when disable the option you can see the function in your language. *** Bug 72927 has been marked as a duplicate of this bug. *** Please Lew, have you solved the issue? I am still having SOME problems with hyperlinks, but those that remain appear to be differences in various versions of Excel. Using the designation
=hyperlink("#'sheetname'!cellID"
appears to solve the problem with post-2002 Excel.
Created attachment 97626 [details] Sample with the hyperlink with functions. Find in the attached file, a sample of the formula in my comment #4. I think if you can set up it properly, it can help to avoid incompatibility issues. Please, do you think we can close as not a bug, and if you find new issues, reopen it?. |