[Solved] Hyperlink ref to cell in same doc does not adj

[dayrelton]

I have a list of names in column A rows 1 thru 10
Each is a hyperlink pointing to a cell in column A where the start of rows data for each name begin.
If I add a row between the list of row for the first name, all the hyperlinks for the rest of the names point to one row above where I expect them to.
In-other-words - the hyperlink seems to hardcode or make absolute the row of the referenced cell I want it to jump to.
If I add a row between the hyperlink and the row in which the target cell is, the hyperlink should increase the referenced row by one.
And decrease for each row deleted.

[acknak]

I assume you're using the HYPERLINK function with a cell reference like "#A5" or "#Sheet2.A5", something like that?

If so, I think you'll have to construct your own link address. the HYPERLINK address is just text and is therefore not adjusted the way references are in formulas.

Maybe there's a better way to do it, but you can make a proper hyperlink address from a formula (in a helper cell somewhere) that's a simple cell reference: =A5, then grab the reference address with =MID(<ref>;2;999) and use that in the HYPERLINK formula: =HYPERLINK("#"&<mid>;"Click here").

The formula will get updated as expected, and the HYPERLINK address will use the new address.
At least that's one approach.

[dayrelton]

I used insert>hyperlink on menu bar.
I selected Document, then selected my document from the dropdown.
In target box I entered the jump-to cell location as A663
In Text box I entered the text displayed in the cell of the hyperlink
[hyperlink cell turns to a grey background - obnoxious in itself]
Also seems like the text value should permit any formula like a regular cell and "hyperlink" is just a separate charactistic of the cell like "conditionals" for example.

So you are saying, use a function instead.
I thought it was strange to have ref the document location for something self-contained in the document.

So strange that such a useful thing is not straightforwardly available.
Just call it JUMPTO(<cell>)
Let me see if I can implement your solution, Thanks

[acknak]

ok. Yes, as far as I know, the Insert > Hyperlink ... path inserts specially-formatted text, and text is never updated.

I do agree with you; that is a problem when you insert a link to a specific cell and the link does not get updated.

A nicer way to do it would be to define a named range and then create the link to the name instead of the raw address. The defined range names are updated when things move. Unfortunately, OO Calc does not support links to named ranges (as far as I recall).

[MrProgrammer]

A nicer way to do it would be to define a named range and then create the link to the name instead of the raw address. The defined range names are updated when things move. Unfortunately, OO Calc does not support links to named ranges (as far as I recall).

I find, using OOo 3.2, that one can use a hyperlink to a defined name =HYPERLINK("#Name"). However the defined name is not updated when rows are inserted. As an alternative, I attach an example of using a helper cell Z1 and the FORMULA() function.

Just call it JUMPTO(<cell>)

How is that going to be significantly different than the HYPERLINK function? HYPERLINK allows you to jump to a cell (including one on another sheet), a defined name, an external document, a bookmark, a web page, etc.

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.

[acknak]

I find, using OOo 3.2, that one can use a hyperlink to a defined name ...

Yep. You're right. I don't know what I was thinking about.

It works fine for me using AOO 4.1.2, and the named range is updated if I insert rows above it so the link still goes to the expected cell.

Thanks for checking!

[dayrelton]

Thanks Mr. Programmer and acknac - it works!
We can only hope the powers that be incorporate a simple JUMPTO(ref) function with full capability to get to other sheets, docs, etc.

[acknak]

It might be a worthwhile enhancement to the HYPERLINK function, to allow direct references as well as a text URI.

[Tutorial] Reporting bugs or suggestions

[IsabelDalhousie]

I'd like to go back to dayreltons original question:

I used insert>hyperlink on menu bar.
I selected Document, then selected my document from the dropdown.
In target box I entered the jump-to cell location as A663
In Text box I entered the text displayed in the cell of the hyperlink
[hyperlink cell turns to a grey background - obnoxious in itself]

I'm trying to solve the obnoxious grey background problem - I've tried looking for default formatting for hyperlinks to I can edit what they look like before and after the hyperlinks are activated but haven't been successfully in getting rid of the obnoxious grey background to the text. Can anyone comment please?

[MrProgrammer]

I'm trying to solve the obnoxious grey background problem

The background color is option OpenOffice → Appearance → Text Documents → Field Shadings. The foreground color is option OpenOffice → Appearance → General → Unvisited Links. Options are set with OpenOffice → Preferences on a Mac, Tools → Options on other platforms. Most people find that using the HYPERLINK function and a cell style is more flexible.

I haven't been successful in getting Calc to recognize that a link has been visited and display it with appearance OpenOffice → Appearance → General → Visited Links.

[RoryOF]

Might /View /Field Shadings be turned on?