Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Preserving hyperlinks when rows are added
In an Excel 2003 workbook, I have several hyperlinks to hlp navigate around.
The difficulty is when additional rows or columns are added they displace the target cell reference. In this situation, is it possible to ensure a hyperlink from a cell takes me to the intended data in a target cell after it is displaced? I'd like to achieve this without the use of macros (if possible). Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Preserving hyperlinks when rows are added
This is always a problem using hyperlinks to navagate within a workbook.
because the links are normally enclosed in double-quotes, they don't adjust as rows/columns/ are added/deleted. But here is a neat trick: A formula like: =HYPERLINK("#Sheet2!I14","go") will "lock" in place, but: =HYPERLINK("#"&CELL("address",Sheet3!Z100),"target ") will adjust ! -- Gary''s Student - gsnu200716 "Jimbob" wrote: In an Excel 2003 workbook, I have several hyperlinks to hlp navigate around. The difficulty is when additional rows or columns are added they displace the target cell reference. In this situation, is it possible to ensure a hyperlink from a cell takes me to the intended data in a target cell after it is displaced? I'd like to achieve this without the use of macros (if possible). Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Preserving hyperlinks when rows are added
And if you're using the Insert|Hyperlink version of hyperlinks, you could give
the target a name (insert|Name|define). Jimbob wrote: In an Excel 2003 workbook, I have several hyperlinks to hlp navigate around. The difficulty is when additional rows or columns are added they displace the target cell reference. In this situation, is it possible to ensure a hyperlink from a cell takes me to the intended data in a target cell after it is displaced? I'd like to achieve this without the use of macros (if possible). Thanks -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Preserving hyperlinks when rows are added
Hi Gary's Student: Can you clarify this formula? I'm assuming "#"
represents the sheet name. What does "address" represent, if sheet represents the name of the worksheet? Also, what is Z100 representing? -- Diana "Gary''s Student" wrote: This is always a problem using hyperlinks to navagate within a workbook. because the links are normally enclosed in double-quotes, they don't adjust as rows/columns/ are added/deleted. But here is a neat trick: A formula like: =HYPERLINK("#Sheet2!I14","go") will "lock" in place, but: =HYPERLINK("#"&CELL("address",Sheet3!Z100),"target ") will adjust ! -- Gary''s Student - gsnu200716 "Jimbob" wrote: In an Excel 2003 workbook, I have several hyperlinks to hlp navigate around. The difficulty is when additional rows or columns are added they displace the target cell reference. In this situation, is it possible to ensure a hyperlink from a cell takes me to the intended data in a target cell after it is displaced? I'd like to achieve this without the use of macros (if possible). Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Preserving hyperlinks when rows are added
HI! Can someone please clarify the formula?
=HYPERLINK("#"&CELL("address",Sheet3!Z100),"target ") What does # & address stand for? "Diana" wrote: Hi Gary's Student: Can you clarify this formula? I'm assuming "#" represents the sheet name. What does "address" represent, if sheet represents the name of the worksheet? Also, what is Z100 representing? -- Diana "Gary''s Student" wrote: This is always a problem using hyperlinks to navagate within a workbook. because the links are normally enclosed in double-quotes, they don't adjust as rows/columns/ are added/deleted. But here is a neat trick: A formula like: =HYPERLINK("#Sheet2!I14","go") will "lock" in place, but: =HYPERLINK("#"&CELL("address",Sheet3!Z100),"target ") will adjust ! -- Gary''s Student - gsnu200716 "Jimbob" wrote: In an Excel 2003 workbook, I have several hyperlinks to hlp navigate around. The difficulty is when additional rows or columns are added they displace the target cell reference. In this situation, is it possible to ensure a hyperlink from a cell takes me to the intended data in a target cell after it is displaced? I'd like to achieve this without the use of macros (if possible). Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Preserving hyperlinks when rows are added
It's a subaddress indicator (from a non-HTML speaking person!).
Next time you're surfing the web, you'll see links that look like this: http://contextures.com/xlDataEntry02.html (in some browsers, you can let the cursor linger over the link and see the address in the status bar at the bottom left). And then there are links that look like: http://contextures.com/xlDataEntry02.html#Fill This second link is on the same page, but goes to a specific location on that web page. Same kind of thing in excel--in this case, you're linking to sheet3, cell z100. Regina wrote: HI! Can someone please clarify the formula? =HYPERLINK("#"&CELL("address",Sheet3!Z100),"target ") What does # & address stand for? "Diana" wrote: Hi Gary's Student: Can you clarify this formula? I'm assuming "#" represents the sheet name. What does "address" represent, if sheet represents the name of the worksheet? Also, what is Z100 representing? -- Diana "Gary''s Student" wrote: This is always a problem using hyperlinks to navagate within a workbook. because the links are normally enclosed in double-quotes, they don't adjust as rows/columns/ are added/deleted. But here is a neat trick: A formula like: =HYPERLINK("#Sheet2!I14","go") will "lock" in place, but: =HYPERLINK("#"&CELL("address",Sheet3!Z100),"target ") will adjust ! -- Gary''s Student - gsnu200716 "Jimbob" wrote: In an Excel 2003 workbook, I have several hyperlinks to hlp navigate around. The difficulty is when additional rows or columns are added they displace the target cell reference. In this situation, is it possible to ensure a hyperlink from a cell takes me to the intended data in a target cell after it is displaced? I'd like to achieve this without the use of macros (if possible). Thanks -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Preserving hyperlinks when rows are added
This is just what I was looking for but is it possible to have the link open
at the top veiwing row instead of somewhere in the middle of the screen? "Dave Peterson" wrote: And if you're using the Insert|Hyperlink version of hyperlinks, you could give the target a name (insert|Name|define). Jimbob wrote: In an Excel 2003 workbook, I have several hyperlinks to hlp navigate around. The difficulty is when additional rows or columns are added they displace the target cell reference. In this situation, is it possible to ensure a hyperlink from a cell takes me to the intended data in a target cell after it is displaced? I'd like to achieve this without the use of macros (if possible). Thanks -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Preserving hyperlinks when burning to CD | Excel Worksheet Functions | |||
I've added hyperlinks to cells, but when I save the file they don' | Excel Discussion (Misc queries) | |||
Rows added after refresh not in formula | Excel Worksheet Functions | |||
Linking a cell to update when rows added | Excel Discussion (Misc queries) | |||
Preserving hyperlinks in linked cells | Excel Discussion (Misc queries) |