Hyperlinks to "Defined Names" shift when rows have been inserted

[Michelle]

Before I ask my specific question, I've copied/pasted a portion of a
previous e-mail that I've sent. It briefly explains my file setup.

I have an excel Workbook that contains several different worksheets. I'm
trying to create a hyperlink from a cell on one worksheet to a cell on another
worksheet. I'm created and defined the names for many of the cells
already--"Level_A", "Level_B", etc--all the way to Z. These are the names
because it's a listing of books by their Alphabetical difficulty level.
(Every book has a level denoted by A-Z).

The problem that I'm having is that once I've set up my Defined Names and
then linked to them, inserting rows on the page that I'm linking to changes
where the hyperlink goes to. What do I need to do so that no matter how
many rows I've entered, the hyperlink ALWAYS takes me to the specific cell
that I've already set. I know that when I set up the "Defined Names" it
refers to a specific numbered cell. I thought that by giving those lines a
"Defined Name" instead of a column#/row# that the hyperlink would follow
even if rows were inserted.

Does this make sense? I want the hyperlink to follow the row and it's
contents (regardless of whether or not the # of the row changes).


Working in Office 2004--with latest 11.2.6 update on a G4 Powerbook (OS
10.3.9)

-- Michelle

 

[CyberTaz]

I'm unclear on how you want this to work. It seems contradictory, but then
again it's late, I'm tired, etc So let's try this:

If you give cell D5 the name of level_A then insert a row above, D5 now
becomes D6 but is still known by the name of level_A - IOW the named range
doesn't stay fixed on the 5th cell in column D.

If you want the link to point to the cell that is *now* D5 you should be
linking to Sheet#!$D$5 rather than linking to the range name of level_A.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[JE McGimpsey]

Does this make sense? I want the hyperlink to follow the row and it's
contents (regardless of whether or not the # of the row changes).

If I understand you correctly, you can define your names (via
Insert/Name/Define...) as indirect references, for instance, instead of

Names in workbook: level_A
Refers to: =Sheet1!$F$6

use

Names in workbook: level_A
Refers to: =INDIRECT("Sheet1!F6")