Description: When the LO Calc function HYPERLINK is used within a cell that is treated as an array formula (Ctrl+Shift+Enter) the text to display shows up in the cell as expected but the text of the Url shows up in the cell below as part of a two-cell array. The lower cell text does not respond to Ctrl+Click and does not show a tool tip popup about Ctrl+Click. Steps to Reproduce: 1.Type =HYPERLINK("#A1","Click me") into the input line (address bar) 2.Press Ctrl+Shift+Enter Actual Results: Click me #A1 in two different cells auto-fixed as a two-cell array formula Expected Results: Click me alone with no spill to the lower cell Reproducible: Always User Profile Reset: No Additional Info: 1. Any information already in the lower cell before performing the steps above will be deleted and replaced with no prompt or warning. 2. This is not a minor inconvenience when creating hyperlinked reports that aggregate data using array formulas since it then requires significant reformatting to hide alternating rows, which cannot be done automatically without macro intervention, and is undone by any row height adjustment.
Created attachment 175680 [details] Example ODS showing HYPERLINK array formula bug This attachment shows a "floating report" that suffers from significant complications because of the HYPERLINK behavior when used in an array formula.
(In reply to Joshua Coppersmith from comment #1) > Created attachment 175680 [details] > Example ODS showing HYPERLINK array formula bug > > This attachment shows a "floating report" that suffers from significant > complications because of the HYPERLINK behavior when used in an array > formula. Does not contain the following macro required to see operation: Function SheetName(sheetIndex As Integer) SheetName=ThisComponent.Sheets(sheetIndex).getName End Function Please quickly add this to the workbook macros with: Tools:Macros:Edit Macros... then in the IDE Click on the workbook name (SimplePlannerBugReport.ods) then: Tools:Organize Macros:Basic... Click on the workbook name again then: Click on New button Type SheetName (literally that text) Click on Ok button Replace macro stub with function given above by marking then pasting, etc. Click Save arrow in IDE Exit IDE...
Hi Eike, I thought you might be interested in this issue
Much simpler reproducer than the attached document: =HYPERLINK("a";"b") entered as array formula results in row1 "b" and row2 "a". Problem is that HYPERLINK() actually returns an array with two elements, URL and cell text, which in array context is evaluated like any other array. We may have to distinguish if the result is final (to be displayed) or to be further evaluated. What does Excel do for this? Or an expression =HYPERLINK("a";"b")&"c" entered as array formula?
Also, same =HYPERLINK("a";"b") =HYPERLINK("a";"b")&"c" what does Excel do if two cells in two rows are selected while entering the formula and closing as array formula?
Thanks to Mike who tried. So, Excel also processes both array elements as usual (i.e. for the second formula produces URL "ac" and cell text "bc"), but for display only uses the cell text, not spilling the URL element, not even if two cells were selected (it just repeats the cell text in that case as if it was a one element array).
Note that extending the cell array to two rows can be prevented by selecting/*marking* only one cell prior to input, as a workaround. Either Ctrl+Click the current cell, or Shift+Down and Shift+Up again, so that it's marked.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/459f9de8a87373c826eadab142850cc3fa578fca Resolves: tdf#145085 HYPERLINK() do not force a 2nd row URL cell in array mode It will be available in 7.3.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
This does not force a 2nd row URL cell in array mode, but keeps the behaviour that if a 2-rows cell array was selected and entered the 2nd row displays the URL and not the repeated cell text, as that may be regarded a feature. To have only a one cell display the previous two cells formula has to be deleted anyway and entered anew as a one cell array formula.
Pending review https://gerrit.libreoffice.org/c/core/+/123635 for 7-2
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-2": https://git.libreoffice.org/core/commit/54a9b7c1db72459f95ed334de34a0e2289329bd2 Resolves: tdf#145085 HYPERLINK() do not force a 2nd row URL cell in array mode It will be available in 7.2.3. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/b2e4123cfd597ef19eebcb1d2a98c058eea990b2 tdf#145085: sc_uicalc: Add unittest It will be available in 7.3.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.