Dynamic Hyperlink

V

vsri2

Hi,
Is it possible to create a Dynamic Hyperlink which updates th
referenced cell information in the same worksheet / workbook.

The problem is if we reference a cell and later insert a row before th
referenced cell, the hyperlink does not get updated. I tried giving
symbols before the cell name ( e.g $A$42) in the create hyperlink too
and also tried the Hyperlink ( ) function.

can you please help
 
1

1fishy

I think you need to update or should I say redo the hyperlink after you
add the row or make any changes to the cell you want to jump to. Hope
this helps.
 
D

David McRitchie

Hi [email protected],

Your normal object hyperlink right click on cell edit
hyperlink would get updated as you insert/delete rows and
columns but you would not generate any hyperlink to a
different cell. So the right-click hyperlink is unsuitable
for you actual usage.

To use the HYPERLINK Worksheet Function takes a
some effort to leave the cell name outside of the double
quotes so that it can be changed, by inserting, deleting
rows and columns, and use the fill-handle. See
http://www.mvps.org/dmcritchie/excel/sheets.htm#hyperlink

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

Excel 2002 and above makes it simpler by the above works in
Excel 97 and up. Hyperlinks were interoduced in Excel 97.

Guess this will be my last reply for a week. .
 
D

DDM

vsri2, the instructions below worked for me in Excel 2003.

To get the dynamic hyperlink you are trying to create, you must reference
the target cell by name, rather than by address.

1-Select the target cell (A42 in your example). Insert > Name > Define.
Enter a name ("target," for example) in the Names in workbook field. Click
OK.

2-In the cell that will hold the hyperlink, press Ctrl-K. Select "Place in
This Document" under Link To. Select the name you defined in Step 1 under
Defined Names. Enter your hyperlink text in text to display. Click OK.

Your hyperlink should link to the same cell even if you add/delete rows
above it.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com
 
D

DDM

vsri2, using the HYPERLINK worksheet function in Excel 2003 (and it probably
works in earlier versions as well), this will give you the dynamic hyperlink
you're looking for:

=HYPERLINK("[workbookname.xls]Worksheet1!CellName","Friendly Name")

Note that the workbook must be saved before you build the formula. Note that
"CellName" in my example refers to a named cell (one that you named using
Insert > Name > Define).

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com
 
V

vsri2

Hi,

Thanx for your reply. By updating I meant, say if I need to jump to th
cell A42. Now I create a hyperlink to it in cell A5. Then I add a ro
before A42. Then if I click on the hyperlink it will still point t
A42. but I want it to point to A43. I know that I could update th
hyperlink manually everytime I add a row. but I wonder if there's a wa
to do it automatically.




David said:
*Hi [email protected],

Your normal object hyperlink right click on cell edit
hyperlink would get updated as you insert/delete rows and
columns but you would not generate any hyperlink to a
different cell. So the right-click hyperlink is unsuitable
for you actual usage.

To use the HYPERLINK Worksheet Function takes a
some effort to leave the cell name outside of the double
quotes so that it can be changed, by inserting, deleting
rows and columns, and use the fill-handle. See
http://www.mvps.org/dmcritchie/excel/sheets.htm#hyperlink

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

Excel 2002 and above makes it simpler by the above works in
Excel 97 and up. Hyperlinks were interoduced in Excel 97.

Guess this will be my last reply for a week. .
---
HTH (Hope This Helps),
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

vsri2 > said:
Hi,
Is it possible to create a Dynamic Hyperlink which updates the
referenced cell information in the same worksheet / workbook.

The problem is if we reference a cell and later insert a row befor the
referenced cell, the hyperlink does not get updated. I tried givin $
symbols before the cell name ( e.g $A$42) in the create hyperlin tool
and also tried the Hyperlink ( ) function.
Click to expand...
Click to expand...
 
V

vsri2

Hi,

Thanx for your reply. By updating I meant, say if I need to jump to th
cell A42. Now I create a hyperlink to it in cell A5. Then I add a ro
before A42. Then if I click on the hyperlink it will still point t
A42. but I want it to point to A43. I know that I could update th
hyperlink manually everytime I add a row. but I wonder if there's a wa
to do it automatically.

Sriram.
*vsri2, using the HYPERLINK worksheet function in Excel 2003 (and i
probably
works in earlier versions as well), this will give you the dynami
hyperlink
you're looking for:

=HYPERLINK("[workbookname.xls]Worksheet1!CellName","Friendly Name")

Note that the workbook must be saved before you build the formula
Note that
"CellName" in my example refers to a named cell (one that you name
using
Insert > Name > Define).

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

vsri2 > said:
Hi,
Is it possible to create a Dynamic Hyperlink which updates the
referenced cell information in the same worksheet / workbook.

The problem is if we reference a cell and later insert a row befor the
referenced cell, the hyperlink does not get updated. I tried givin $
symbols before the cell name ( e.g $A$42) in the create hyperlin tool
and also tried the Hyperlink ( ) function.

can you please help?
Click to expand...
Click to expand...
 
V

vsri2

Hi,

Thanx for your reply. By updating I meant, say if I need to jump to th
cell A42. Now I create a hyperlink to it in cell A5. Then I add a ro
before A42. Then if I click on the hyperlink it will still point t
A42. but I want it to point to A43. I know that I could update th
hyperlink manually everytime I add a row. but I wonder if there's a wa
to do it automatically.

Sriram
 
V

vsri2

Hi,

Thanx for your reply. By updating I meant, say if I need to jump to th
cell A42. Now I create a hyperlink to it in cell A5. Then I add a ro
before A42. Then if I click on the hyperlink it will still point t
A42. but I want it to point to A43. I know that I could update th
hyperlink manually everytime I add a row. but I wonder if there's a wa
to do it automatically.

Sriram.
 
V

vsri2

Hi ,

Sorry if this message is duplicated to you.

Thanx for your reply. By updating I meant, say if I need to jump to th
cell A42. Now I create a hyperlink to it in cell A5. Then I add a ro
before A42. Then if I click on the hyperlink it will still point t
A42. but I want it to point to A43. I know that I could update th
hyperlink manually everytime I add a row. but I wonder if there's a wa
to do it automatically.

Thanks for your help.

Sriram
 
D

Dave Peterson

Did you try David's first suggestion?

It worked ok for me.

vsri2 < said:
Hi,

Thanx for your reply. By updating I meant, say if I need to jump to the
cell A42. Now I create a hyperlink to it in cell A5. Then I add a row
before A42. Then if I click on the hyperlink it will still point to
A42. but I want it to point to A43. I know that I could update the
hyperlink manually everytime I add a row. but I wonder if there's a way
to do it automatically.

David said:
*Hi [email protected],

Your normal object hyperlink right click on cell edit
hyperlink would get updated as you insert/delete rows and
columns but you would not generate any hyperlink to a
different cell. So the right-click hyperlink is unsuitable
for you actual usage.

To use the HYPERLINK Worksheet Function takes a
some effort to leave the cell name outside of the double
quotes so that it can be changed, by inserting, deleting
rows and columns, and use the fill-handle. See
http://www.mvps.org/dmcritchie/excel/sheets.htm#hyperlink

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

Excel 2002 and above makes it simpler by the above works in
Excel 97 and up. Hyperlinks were interoduced in Excel 97.

Guess this will be my last reply for a week. .
---
HTH (Hope This Helps),
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

vsri2 > said:
Hi,
Is it possible to create a Dynamic Hyperlink which updates the
referenced cell information in the same worksheet / workbook.

The problem is if we reference a cell and later insert a row before the
referenced cell, the hyperlink does not get updated. I tried giving $
symbols before the cell name ( e.g $A$42) in the create hyperlink tool
and also tried the Hyperlink ( ) function. *
Click to expand...
Click to expand...
Click to expand...
 
Top