[Solved] Hyperlink in 1 cell to go to another cell

Discuss the spreadsheet application
Post Reply
stonecold
Posts: 3
Joined: Thu Aug 04, 2011 9:00 am

[Solved] Hyperlink in 1 cell to go to another cell

Post by stonecold »

Hi,
I am trying to provide a hyperlink in a cell that when clicked, will bring the focus to the referenced cell, which would be maybe much more below in a page.

The hyperlink is to be created in the cell using a macro I have written.

Can anybody provide some reference or examples on how can this be done, if possible?

Thanks in advance.
Last edited by stonecold on Fri Jan 06, 2012 7:56 am, edited 1 time in total.
OpenOffice 2.4 on Windows XP SP3
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: hyperlink in 1 cell to go to another cell

Post by Charlie Young »

stonecold wrote:Hi,
I am trying to provide a hyperlink in a cell that when clicked, will bring the focus to the referenced cell, which would be maybe much more below in a page.

The hyperlink is to be created in the cell using a macro I have written.

Can anybody provide some reference or examples on how can this be done, if possible?

Thanks in advance.
No macro is necessary, the Help shows how to use the hyperlink function for this sort of thing.

To go to Sheet1.A1:

Code: Select all

=HYPERLINK("#Sheet1.A1";"Go to top")
Of course, macros may be used for this as well, though it can get tricky. Here's one to put a link in the current selection to link to sheet1.a1:

Code: Select all

Sub LinkToA1
	Dim oDoc As Object
	Dim oSheet As Object
	Dim oCell As Object
	Dim oCursor As Object
	Dim oText
	Dim tField
	oDoc = ThisComponent
	oCell = oDoc.CurrentController.Selection
	oCell.String = "Goto Sheet1.A1"
	tField = oDoc.createInstance( "com.sun.star.text.textfield.URL" )
	tField.Representation = "Sheet1.A1"
	tField.URL = "#Sheet1.A1"
	oText = oCell.getText()
	oCursor = oText.createTextCursor
	oCursor.gotoStart(False)
	oCursor.goRight(5,False)
	oCursor.goRight(Len(tField.Representation),True)
	oText.insertTextContent(oCursor,tField,True)
End Sub

You can also use Insert > Hyperlink from the menu, though that only works to goto named ranges, as I recall.
Apache OpenOffice 4.1.1
Windows XP
gg39au
Posts: 3
Joined: Tue Jul 15, 2008 9:41 pm

Re: [Solved] Hyperlink in 1 cell to go to another cell

Post by gg39au »

I wish to use a hyperlink to go from one cell to a cell that I have NAMED. I don’t mean a name like A1 I mean a name obtained by Insert – Name – Define.

The idea is to build a table of contents.

In Excel97 it is possible to bookmark a cell using “Select a cell. Insert. Name. Define.”

Then one can go to this cell from another part of the spreadsheet (say from the table of contents) by pressing cntrl+K, clicking on Named location in file, Browse, Defined name.

What is the corresponding procedure in Open Office please?

George
OOo 2.3.X on MS Windows Vista
User avatar
karolus
Volunteer
Posts: 1227
Joined: Sat Jul 02, 2011 9:47 am

Re: [Solved] Hyperlink in 1 cell to go to another cell

Post by karolus »

hi
gg39au wrote:I wish to use a hyperlink to go from one cell to a cell that I have NAMED. I don’t mean a name like A1 I mean a name obtained by Insert – Name – Define.

The idea is to build a table of contents.

In Excel97 it is possible to bookmark a cell using “Select a cell. Insert. Name. Define.”

Then one can go to this cell from another part of the spreadsheet (say from the table of contents) by pressing cntrl+K, clicking on Named location in file, Browse, Defined name.

What is the corresponding procedure in Open Office please?

George
Whats the problem ? - Define for Example a Cell with Name Chapter10.( short way: select cell, write the Name into Addressbox above Column A, and hit 'enter'-key )
In your "Table of Contents" put in the Formula

Code: Select all

 = HYPERLINK("#Chapter10";"Chapter10")
Btw: Calc has an "Navigator" - you don't need create a "Table of Contents" !

Karo
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 24.8… flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Post Reply