[Dropped] Hyperlinks within same doc/sheet are absolute

Discuss the spreadsheet application
Post Reply
boardbro
Posts: 17
Joined: Thu Aug 13, 2015 11:48 am

[Dropped] Hyperlinks within same doc/sheet are absolute

Post by boardbro »

I am using version 4.1.10 in Windows 10, not the Windows XP version shown. (I am going to change that very old info in my user control panel real real soon, just figuring out exactly where/how).

I have a ledger and it's divided into a dozen or so sections. Why can't I Insert a Bookmark at the top cell of each such section. The "Insert bookmark" command isn't even available ie not present in the Menu!? As an alternative, I insert a hyperlink in my "Table of Contents" section that sits atop the spreadsheet. But the reference is absolute, dang it!; it does not change in response to adding or deleting intervening lines; and that occurs daily. I find this situation pretty darn exasperating. Is there a workaround please? No matter how complicated a workaround, I will go that route. Many thanks you all.
Last edited by boardbro on Fri Jun 11, 2021 3:32 pm, edited 2 times in total.
AOO 4.1.10 in Windows 10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Aghast that my Hyperlinks within same doc/sheet are abso

Post by FJCC »

I would do this using named cells and the Navigator. To name a cell, click on it then go to the menu Insert -> Names -> Define. Give it a name in that dialog. You can access the Navigator in several ways. The icon for the Navigator is a compass. The Sidebar (View -> Sidebar if it is not visible) has one, there is one on the main toolbar, you can press F5, or use the menu View -> Navigator. The Navigator has an expandable list called Range Names. On that list you will see all the cells names you have assigned. Double click on one and you can jump to that cell and the reference will change if you insert rows.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
boardbro
Posts: 17
Joined: Thu Aug 13, 2015 11:48 am

Re: Aghast that my Hyperlinks within same doc/sheet are abso

Post by boardbro »

Just perfect! I love you!
AOO 4.1.10 in Windows 10
boardbro
Posts: 17
Joined: Thu Aug 13, 2015 11:48 am

Re: Aghast that my Hyperlinks within same doc/sheet are abso

Post by boardbro »

By the way, I still do not see how, in my user control panel, to rid my profile of that old old claim that I have a WXP machine. Also, I don't readily see how to mark this thread Solved. Your assistance requested.
AOO 4.1.10 in Windows 10
boardbro
Posts: 17
Joined: Thu Aug 13, 2015 11:48 am

Re: Aghast that my Hyperlinks within same doc/sheet are abso

Post by boardbro »

I need a tad more help with that please. Within the Navigator I can select an entry, one of my dozen or so sections, and then punch "Insert as Hyperlink". So I tried that, yet nothing whatsoever appeared in the cell that I had selected/highlighted prior to the operation. What gives??
Last edited by boardbro on Fri May 28, 2021 11:00 pm, edited 2 times in total.
AOO 4.1.10 in Windows 10
boardbro
Posts: 17
Joined: Thu Aug 13, 2015 11:48 am

Re: Aghast that my Hyperlinks within same doc/sheet are abso

Post by boardbro »

And ok I just this second noticed your trailer explaining how to mark my thread Solved, thank you. But for now I am holding off doing so.
AOO 4.1.10 in Windows 10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Aghast that my Hyperlinks within same doc/sheet are abso

Post by FJCC »

With Insert As Hyperlink selected, drag the name of the cell from the Range Names list to the location where you want the hyperlink.

To change your signature, click on User Control Panel near the top of the screen, then click on the Profile tab and then click on Edit Signature.
 Edit: Signature updated for OP by MrProgrammer 
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
boardbro
Posts: 17
Joined: Thu Aug 13, 2015 11:48 am

Re: Aghast that my Hyperlinks within same doc/sheet are abso

Post by boardbro »

Of course! ... I should've remembered: I placed that info in my signature -- a decade ago :lol:
Well thanks, It has truly been a pleasure. :)

BTW inexplicably, it wouldn't let me emend the thread's subject. All is solved and my Table of Contents looks fab. Bye. :super:
boardbro
Posts: 17
Joined: Thu Aug 13, 2015 11:48 am

Re: Hyperlinks within same doc/sheet are absolute

Post by boardbro »

I've changed this thread from solved to unsolved because my original balk was that inserting a hyperlink resulted in a link that failed to adjust for lines inserted or deleted in the interim area. But don't get me wrong: I am still extremely grateful for the advice I received from FJCC here as it added to my edification greatly. However, the ranges that I have defined as a basis for a Table of Contents still drift around vertically in reaction to my regularly adding and/or deleting lines, though I haven't been keen enough to ascertain the precise causal pattern; I mean, some of the links are still true to their original definition. 'Must be some bug(s) in OpenOffice. Oh well, fortunately this project is just for my personal record-keeping and not anything pivotal such as a work-related thing would be. Alas! I won't sweat it if you don't.
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Hyperlinks within same doc/sheet are absolute

Post by robleyd »

Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
boardbro
Posts: 17
Joined: Thu Aug 13, 2015 11:48 am

Re: Hyperlinks within same doc/sheet are absolute

Post by boardbro »

Thank you to last two responders. I shall definitely pursue this further ... but at my own likely-slow pace. Really, this is great elucidation!
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Hyperlinks within same doc/sheet are absolute

Post by Villeroy »

How to calculate a relative hyperlink pointing to the cell 12 rows down and 6 columns to the right:

Code: Select all

=HYPERLINK("#" & ADDRESS(ROW()+12;COLUMN()+6)&T(STYLE("Hyperlink"))
with cell style "Hyperlink" including some special color, font and underlining so it looks like a hyperlink.
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
Bill
Volunteer
Posts: 8932
Joined: Sat Nov 24, 2007 6:48 am

Re: Hyperlinks within same doc/sheet are absolute

Post by Bill »

boardbro wrote:...the ranges that I have defined as a basis for a Table of Contents still drift around vertically in reaction to my regularly adding and/or deleting lines,
I don't understand the problem. If you insert or delete rows above defined ranges, then the range should move by the amount of rows inserted or deleted. The address of the range is changed automatically to indicate the new position of the range.

By the way, do you really need a hyperlink? Defined names appear in the Name Box right above the first column. Clicking a name in the Name Box highlights the defined range and selects the first cell in the range.
AOO 4.1.14 on Ubuntu MATE 22.04
Post Reply