Apache OpenOffice Community Forum

Hi I would like to get some help with the following problem:

I have a spreadsheet which is organized in a way that I have the titles at the top of the seet like a content sheet, and then by clicking on any of the title you would automathically be directed to a the relevant cell a few hundred rows below.
so good so far , my problem comes when I add an extra row as the wor nubers change and the hyperlink does not seem to follow my target cell.

I am using this formula =HYPERLINK("#spreadsheet.Q86";"go to")

I hope it is some very simple to answer , and I could get a quick help as I am desperate with my work and kind of stuck now....

Thank you for the help in advance

Hi, and welcome to the forum.

george9999 wrote:My problem comes when I add an extra row as the wor nubers change and the hyperlink does not seem to follow my target cell.
I am using this formula =HYPERLINK("#spreadsheet.Q86";"go to")

Of course since "#spreadsheet.Q86" is text and text is never updated when cells are moved.

george9999 wrote:I hope it is some very simple to answer

In your target cell, say Q86, use Insert → Names → Define and give a name to this location, say Hogwarts. Change your hyperlink to =HYPERLINK("#Hogwarts";"go to"). When you insert rows, Calc will update the defined name to point to the new location.

Read about defined names in Help → Index or in User Guides (PDF) or searching for topics about it in the Calc Forum

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

george9999 wrote:hyperling seet automathically wor nubers

A forum hint: Proofread your posts before submitting. Sloppy posts are more likely to be ignored. Showing that you took care in posting tells volunteers it is worth their time to provide an answer.

[Tutorial] Ten concepts that every Calc user should know

It works!!! thank you very much!