Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
recalculate hyperlink location
I have a spreadsheet that changes often. After inserting or deleting a line
the location of my hyperlinks change. Is there any way to automatically recalculate the location of the hyperlinks instead of changing then all manually? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
recalculate hyperlink location
If you are setting a hyperlink to a place in your worksheet, use a Named
Range rather than an absolute address: Instead of: =HYPERLINK("#Sheet3!$A$1") use something like: =HYPERLINK("#"&CELL("address",alpha),alpha) where alpha has been set with: Insert names Define... The trick is that the Name adjusts as rows/columns are inserted/deleted. -- Gary''s Student - gsnu200841 "Goforth" wrote: I have a spreadsheet that changes often. After inserting or deleting a line the location of my hyperlinks change. Is there any way to automatically recalculate the location of the hyperlinks instead of changing then all manually? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
recalculate hyperlink location
Naming the target of the hyperlink makes those Insert|Hyperlink type hyperlinks
work much better. But if the OP is using the worksheet function, he or she doesn't need to name the range. This kind of formula will adjust, too: =HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5) Gary''s Student wrote: If you are setting a hyperlink to a place in your worksheet, use a Named Range rather than an absolute address: Instead of: =HYPERLINK("#Sheet3!$A$1") use something like: =HYPERLINK("#"&CELL("address",alpha),alpha) where alpha has been set with: Insert names Define... The trick is that the Name adjusts as rows/columns are inserted/deleted. -- Gary''s Student - gsnu200841 "Goforth" wrote: I have a spreadsheet that changes often. After inserting or deleting a line the location of my hyperlinks change. Is there any way to automatically recalculate the location of the hyperlinks instead of changing then all manually? Thanks -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
recalculate hyperlink location
"Gary''s Student" wrote: If you are setting a hyperlink to a place in your worksheet, use a Named Range rather than an absolute address: Instead of: =HYPERLINK("#Sheet3!$A$1") use something like: =HYPERLINK("#"&CELL("address",alpha),alpha) where alpha has been set with: Insert names Define... The trick is that the Name adjusts as rows/columns are inserted/deleted. -- Gary''s Student - gsnu200841 "Goforth" wrote: I have a spreadsheet that changes often. After inserting or deleting a line the location of my hyperlinks change. Is there any way to automatically recalculate the location of the hyperlinks instead of changing then all manually? Thanks I can't thank you enough! You've save me a lot of time. Glenn |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
recalculate hyperlink location
"Dave Peterson" wrote: Naming the target of the hyperlink makes those Insert|Hyperlink type hyperlinks work much better. But if the OP is using the worksheet function, he or she doesn't need to name the range. This kind of formula will adjust, too: =HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5) Gary''s Student wrote: If you are setting a hyperlink to a place in your worksheet, use a Named Range rather than an absolute address: Instead of: =HYPERLINK("#Sheet3!$A$1") use something like: =HYPERLINK("#"&CELL("address",alpha),alpha) where alpha has been set with: Insert names Define... The trick is that the Name adjusts as rows/columns are inserted/deleted. -- Gary''s Student - gsnu200841 "Goforth" wrote: I have a spreadsheet that changes often. After inserting or deleting a line the location of my hyperlinks change. Is there any way to automatically recalculate the location of the hyperlinks instead of changing then all manually? Thanks -- Dave Peterson Dave, Thanks to both of you. This is exactly what I was looking for! Glenn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlink to workbook location | Excel Worksheet Functions | |||
File Location Hyperlink | Setting up and Configuration of Excel | |||
Hyperlink to paste in other location | Links and Linking in Excel | |||
Browsing to Hyperlink location | Excel Discussion (Misc queries) | |||
Hyperlink Location | Links and Linking in Excel |