Hyperlink does not adjust or move as rows are added and deleted

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an excel worksheet.

Sheet one acts as a table of contents.
Sheet two contains 8,000 rows of part numbers

I have set hyperlinks from sheet one to sheet two. In sheet one you click on
a prodcut category and it takes you to the start of that section on sheet
two.

As I add rows or delete rows in sheet two the hyperlink remains constant and
does not move. Thus the hyperlinks becomes inaccurate.

How do I make the hyperlink move/flaot with the cell as I add and delete
rows to sheet two.

My cell links for information flaot/automatically adjust just fine. Its just
the hyperlinks which never adjust.
 
Hi Harvey,

If there are not too many categories, define a name for the start of each category and use that in Insert>Hyperlink>Place in this
Document>Defined names

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have an excel worksheet.
|
| Sheet one acts as a table of contents.
| Sheet two contains 8,000 rows of part numbers
|
| I have set hyperlinks from sheet one to sheet two. In sheet one you click on
| a prodcut category and it takes you to the start of that section on sheet
| two.
|
| As I add rows or delete rows in sheet two the hyperlink remains constant and
| does not move. Thus the hyperlinks becomes inaccurate.
|
| How do I make the hyperlink move/flaot with the cell as I add and delete
| rows to sheet two.
|
| My cell links for information flaot/automatically adjust just fine. Its just
| the hyperlinks which never adjust.
 
If you want to continue to use Insert|Hyperlink style hyperlinks, you could name
each of the targets and refer to them in the Insert|hyperlink dialog.

I like to use the =hyperlink() worksheet function.

David McRitchie posted this and it might help you:

=HYPERLINK("#"&CELL("address",C5),C5)
=HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5)
=HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)

Since they're just formulas, they'll adjust when you insert/delete rows or
columns.
 
Back
Top