Download it now!
Bug 62289 - Adding hyperlinks via the GUI to other sheets with only a number for the name in the same workbook take you to the ROW number in the current sheet, instead of to the target_sheet.a1
Summary: Adding hyperlinks via the GUI to other sheets with only a number for the name...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Hyperlink-Calc
  Show dependency treegraph
 
Reported: 2013-03-13 11:57 UTC by Charles
Modified: 2019-10-30 07:27 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments
test document (7.33 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-03-14 15:14 UTC, Petr Mladek
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Charles 2013-03-13 11:57:22 UTC
A hyperlink defined on one worksheet that is linked to another sheet/tab in the same workbook that is named only with a number, when clicked, takes you to the ROW NUMBER in the same sheet, instead of the desired sheet.

Steps to reproduce:

1. Create a new/blank worksheet

2. Create an additional sheet

3. Rename the sheet to any number

4. Add a hyperlink in sheet1, and make the target one of the other worksheets that has a number for the name.

5. Click the hyperlink

6. Instead of taking you to the desired sheet, you go to the ROW that has the same number as the sheet you are trying to go to.
Comment 1 Petr Mladek 2013-03-14 15:14:00 UTC
Created attachment 76530 [details]
test document

I have created the hyperlink by:

    + open "Insert/Hyperlink"
    + select the tab "Document"
    + press the button "Target in Document" (dot inside a circle icon)
    + select "100" in "Sheets" tree and press "Apply"
    + enter "text", "name", and press apply
Comment 2 Petr Mladek 2013-03-14 15:42:50 UTC
I see the same problem also with LO-3.4.6. I did not test earlier versions. I wonder if this ever worked.

A workaround is to prefix the numbers with some letters, e.g. to use "day100" or "group100", "car100" instead of "100" as the sheet name.

It is pity the the link is "#100". There is no difference between links to cells or sheets.
I add Calc developers into CC to look at it. I wonder if there is an alternative syntax that might work even with the number.

Otherwise, it is old bug, workaround exists => this bug could not suddenly start blocking releases with other useful fixes and improvements => lowering the severity a bit.
Comment 3 Charles 2013-03-14 16:12:44 UTC
@Petr,

Yes, I know this used to work, because I built these spreadsheets (links and all) in Libreoffice...

So it is definitely a regression, I just don't know for sure when it crept in...
Comment 4 Charles 2013-03-14 16:16:06 UTC
(In reply to comment #2)
> A workaround is to prefix the numbers with some letters, e.g. to use
> "day100" or "group100", "car100" instead of "100" as the sheet name.

Not a very good workaround if you have dozens or hundreds of workbooks with links like this already created.

> Otherwise, it is old bug, workaround exists => this bug could not suddenly
> start blocking releases with other useful fixes and improvements => lowering
> the severity a bit.

I understand why you did this, but the facts is, this *is* a blocker for anyone who has lots of workbooks with links like this.

If/when my boss finds out about this, that would be just one more reason in support of the vocal minority of users here who want us to switch to Microsoft Office.
Comment 5 Eike Rathke 2013-03-14 16:24:11 UTC
The full syntax to address a cell is #Sheet.ColRow, so for you example to jump to sheet 100 cell A1 it would be #100.A1 and that works. Giving only a number is a shortcut to jump to a row in the same sheet. This is not a bug.
Comment 6 Charles 2013-03-14 17:07:36 UTC
Hmmm....

Looking back through our older worksheets, I do indeed have a cell refernce as well...

I could have sworn I had used just a number before...

Oh well, sorry for the noise...
Comment 7 Steve Edmonds 2013-03-15 00:50:30 UTC
It seems it is just adding the hyperlink with the target button (on the insert hyperlink window) that is broken. When you select a sheet it inserts, say 2012 and it should insert 2012.a1 as a minimum.

Reopened this but you could argue it is a different bug. 
The title " Hyperlinks to other sheets with only a number for the name in the same workbook take you to the ROW number in the current sheet, instead of to the desired sheet" could be changed to

" Hyperlinks to other sheets with only a number for the name in the same workbook  and inserted with the Target button take you to the ROW number in the current sheet, instead of to the desired sheet"
Comment 8 Steve Edmonds 2013-03-15 00:53:06 UTC
And if you follow  Petr Mladek's process it will fail.
Comment 9 Charles 2013-03-15 10:09:25 UTC
Thanks Steve... I think you are correct. Virtually all of our links are to the .a1 cell on the target sheet. A few I manually configured to a specific cell in the target sheet, but most are to .a1. I know that the GUI/Target button is always how I've added the hyperlinks, so this explains things.
Comment 10 Charles 2013-03-15 10:09:50 UTC Comment hidden (obsolete)
Comment 11 V Stuart Foote 2013-03-18 16:58:34 UTC
Confirmed in 4.0.2 rc1, the issue is exactly as described in the summary.
The problem is in interpreting an incomplete hyper link.

1) If the complete hyper link is entered Sheet 2.A2 it becomes #Sheet 2.A2 And movement to that sheet and cell is reliable.

2) If you insert the hyperlink without the cell reference, e.g. #Sheet 2,  it takes you that sheet but looks to retain the last cell referenced, i.e. the landing cell is erratic.

3) And if as reported by OP, even if you use the GUI and select the numbers only page name, e.g. 3 so hyperlink is #3 ,the link is interpreted incorrectly and you remain on the same page--and apparently in that numbered row.

So there is definitely an issue with the Target in Document GUI picker allowing an incomplete hyper link. Or equally an inconsistent parser/handling logic for incomplete hyperlink targets--would think that absent a complete hyperlink target, the parser should identify the sheet and default to a consistent cell--A1 I would imagine.
Comment 12 V Stuart Foote 2013-03-18 19:50:56 UTC
(In reply to comment #5)
@Eike,

> The full syntax to address a cell is #Sheet.ColRow, so for you example to
> jump to sheet 100 cell A1 it would be #100.A1 and that works. Giving only a
> number is a shortcut to jump to a row in the same sheet. This is not a bug.

With the default row short cut you describe, what cell should you end up in in the numbered row?

Also, this issue is occurring when using the Insert > Hyperlink GUI sheet picker for a "Target in document". The hyperlink entered as a number should not behave that way from the sheet picker--since it is a sheet name that is valid. Why should an incomplete hyperlink to a sheet with a numeric name not work, but an incomplete hyperlink to a sheet with a string name work--sort of. So suspect there is a test for string that is failing. And neither incomplete hyperlink takes you to the cell in a sheet where you'd expect which could lead to unwelcome results.

Suggest the "Target in Document" validation logic for sheets needs a little tweaking to not generate a hyperlink that is fully addressed--rather than leaving a number that gets interpreted as a row short cut.
Comment 13 Joel Madero 2014-11-02 16:18:05 UTC
Never independently confirmed by QA team. Moving to UNCONFIRMED as REOPENED is incorrect.
Comment 14 raal 2014-11-02 19:19:17 UTC
I can confirm with Version: 4.4.0.0.alpha1+
Build ID: 04ea7b24ec1b5a027efa0b850f2bc3ac7116c52e
TinderBox: Linux-rpm_deb-x86_64@46-TDF, Branch:master, Time: 2014-10-31_23:27:49

When using GUI for target then #sheet.A1 should be used. Setting as NEW, version 3.5 - oldest version I have and bug occurs in this version.
Comment 15 QA Administrators 2015-12-20 16:12:48 UTC Comment hidden (obsolete)
Comment 16 QA Administrators 2017-01-03 19:48:13 UTC Comment hidden (obsolete)
Comment 17 Eike Rathke 2017-02-22 16:03:10 UTC
(In reply to V Stuart Foote from comment #12)
> With the default row short cut you describe, what cell should you end up in
> in the numbered row?
The same column the cell cursor is in when clicking on the hyperlink.


(In reply to V Stuart Foote from comment #11)
> 2) If you insert the hyperlink without the cell reference, e.g. #Sheet 2, 
> it takes you that sheet but looks to retain the last cell referenced, i.e.
> the landing cell is erratic.
It is not erratic, it takes one to the last used cell cursor position in that sheet.

> 3) And if as reported by OP, even if you use the GUI and select the numbers
> only page name, e.g. 3 so hyperlink is #3 ,the link is interpreted
> incorrectly and you remain on the same page--and apparently in that numbered
> row.
It is not interpreted incorrectly. That's what a number as target specifies. If we want to distinguish between a numeric sheet name and a row number we'll need a different target format for a numeric sheet name.

> So there is definitely an issue with the Target in Document GUI picker
> allowing an incomplete hyper link. Or equally an inconsistent
> parser/handling logic for incomplete hyperlink targets--would think that
> absent a complete hyperlink target, the parser should identify the sheet and
> default to a consistent cell--A1 I would imagine.
I don't think so. For a target of only a sheet name I consider jumping to the last used place a valuable feature. Picking a sheet from the Navigator should not result in a specific address in general. We could do so if the sheet name was numeric and append .A1 then. Better might be to enquote such a sheet name in single quotes (#'100') and upon jumping to the target evaluate that. Note that this is not only through the Link dialog but also when Drag&Drop'ing a sheet name from the Navigator to a cell.
Comment 18 QA Administrators 2018-07-24 02:36:57 UTC Comment hidden (obsolete)
Comment 19 HJ 2019-10-25 00:31:19 UTC
(In reply to QA Administrators from comment #18)

Still reproducible in:
版本:6.3.2.2 (x64)
組建 ID:98b30e735bda24bc04ab42594c85f7fd8be07b9c
CPU 執行緒:8; OS:Windows 10.0; UI 算繪:預設; VCL: win; 
語言地區:zh-TW (zh_TW); UI語言:zh-TW
Calc: threaded
Comment 20 Franklin Weng 2019-10-25 00:40:14 UTC
(In reply to QA Administrators from comment #18)

Also in

版本:6.4.0.0.alpha1
組建 ID:cc57df8f942f239d29cb575ea5a7cb01405db787
CPU 執行緒:4; OS:Linux 5.2; UI 算繪:預設; VCL: kf5; 
語言地區:zh-TW (zh_TW.UTF-8); UI-Language: zh-TW
Calc: threaded

Looks like an UX option.  When adding hyperlink and selecting the target in the current document, maybe take a look if the name matches any cell index.  If so add an additional check for the real target.

Add needsUXEval.
Comment 21 Heiko Tietze 2019-10-29 11:24:14 UTC
(In reply to Franklin Weng from comment #20)
> Looks like an UX option.

Rather an UX issue. You can also name the second sheet "S2" and you will jump to cell S2 instead of the worksheet. Feels like an easter egg but from last year and rotten since the intended function is not accessible anymore.

If cell references are needed as target I would request it to be entered in the formula syntax $S$2 and allow to enter Sheet2:A2 or Sheet2:$A$2 (both result in invalid range). If cell targets are not so important we could disable direct editing and never target cells.
Comment 22 Eike Rathke 2019-10-29 11:30:23 UTC
Sorry, no, a cell reference like S2 always was a valid jump target and should be. And of course Sheet2:A2 is invalid because it should be Sheet2.A2 instead and that works.
Comment 23 Eike Rathke 2019-10-29 11:33:53 UTC
Selecting a sheet from a sheet tree (per comment 2) probably should not only use the sheet name (100) as target but form a proper reference like '100'.A1
Comment 24 Heiko Tietze 2019-10-29 11:48:37 UTC
(In reply to Eike Rathke from comment #22)
> Sorry, no, a cell reference like S2 always was a valid jump target
(In reply to Eike Rathke from comment #23)
> proper reference like '100'.A1

So resolve as WFM?
Comment 25 Eike Rathke 2019-10-29 17:41:46 UTC
(In reply to Heiko Tietze from comment #24)
> So resolve as WFM?
No. Clicking on a sheet in the sheet tree should produce a target enabling a jump to that sheet, however accomplished, which according to this bug it doesn't as only the sheet name is used which in the case it is a number is also a valid row target. (didn't try if a sheet name S equally jumps to column S instead, but would be logical).
Comment 26 Heiko Tietze 2019-10-30 07:27:17 UTC
(In reply to Eike Rathke from comment #25)
> ...sheet name S equally jumps to column S instead

It does. So in case of ambiguous sheet names / tree selection (or always), the target should take the full reference like "100.A1".