Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
hyperlink to a cell: adjusting for changes?
(Excel 2002)
I recently discovered that Excel can hyperlink to another cell in the same workbook, which looks to be very useful. In this re, ... Using (row,col) designation, the link's 'reference' (using the word loosly) appears to be absolute: If I set it to 'A31', and then insert/delete row 29, the link will still be to 'A31' - as if I'd spec'd $A$31, in most other contexts. I know I can get around this by referencing a 'named' target cell. This is OK, ... but, a litte more effort. So, just to be sure I'm not missing something, is there a way to specify a (row,col)-type hyperlink, so that it gets adjusted when insert/delete changes move the target cell? Thanks, George |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
hyperlink to a cell: adjusting for changes?
Try this:
=HYPERLINK("[My Workbook.xls]'My Sheet'!"&ADDRESS(ROW('My Sheet'!A31),COLUMN('My Sheet'!A31)),"Named Link") Now as the cell A31 on My Sheet moves around, the hyperlink will adjust accordingly. -- Best Regards, Luke M "George" wrote in message ... (Excel 2002) I recently discovered that Excel can hyperlink to another cell in the same workbook, which looks to be very useful. In this re, ... Using (row,col) designation, the link's 'reference' (using the word loosly) appears to be absolute: If I set it to 'A31', and then insert/delete row 29, the link will still be to 'A31' - as if I'd spec'd $A$31, in most other contexts. I know I can get around this by referencing a 'named' target cell. This is OK, ... but, a litte more effort. So, just to be sure I'm not missing something, is there a way to specify a (row,col)-type hyperlink, so that it gets adjusted when insert/delete changes move the target cell? Thanks, George |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
hyperlink to a cell: adjusting for changes?
Presumably you mean in another workbook.
If this is the case then have both workbooks open when making the changes and excel will do all the work for you. "George" wrote: (Excel 2002) I recently discovered that Excel can hyperlink to another cell in the same workbook, which looks to be very useful. In this re, ... Using (row,col) designation, the link's 'reference' (using the word loosly) appears to be absolute: If I set it to 'A31', and then insert/delete row 29, the link will still be to 'A31' - as if I'd spec'd $A$31, in most other contexts. I know I can get around this by referencing a 'named' target cell. This is OK, ... but, a litte more effort. So, just to be sure I'm not missing something, is there a way to specify a (row,col)-type hyperlink, so that it gets adjusted when insert/delete changes move the target cell? Thanks, George . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
hyperlink to a cell: adjusting for changes?
If you're using the Insert|hyperlink version of hyperlinks, then you'll want to
name that target. These hyperlinks don't adjust when you delete/insert rows or columns. If you're using the =hyperlink() worksheet function, you could make sure that you open both (all???) files before you make changes. (Assuming that your formulas are straightforward.) But if you don't want to open that workbook with those =hyperlink() formulas, then you'll want to name the targets. George wrote: (Excel 2002) I recently discovered that Excel can hyperlink to another cell in the same workbook, which looks to be very useful. In this re, ... Using (row,col) designation, the link's 'reference' (using the word loosly) appears to be absolute: If I set it to 'A31', and then insert/delete row 29, the link will still be to 'A31' - as if I'd spec'd $A$31, in most other contexts. I know I can get around this by referencing a 'named' target cell. This is OK, ... but, a litte more effort. So, just to be sure I'm not missing something, is there a way to specify a (row,col)-type hyperlink, so that it gets adjusted when insert/delete changes move the target cell? Thanks, George -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
hyperlink to a cell: adjusting for changes?
But if you don't want to open that workbook with those =hyperlink() formulas,
then you'll want to name the targets. Should have been: But if you don't want to open those target workbooks that your =hyperlink() formulas point to, then you'll want to name the targets. Dave Peterson wrote: If you're using the Insert|hyperlink version of hyperlinks, then you'll want to name that target. These hyperlinks don't adjust when you delete/insert rows or columns. If you're using the =hyperlink() worksheet function, you could make sure that you open both (all???) files before you make changes. (Assuming that your formulas are straightforward.) But if you don't want to open that workbook with those =hyperlink() formulas, then you'll want to name the targets. George wrote: (Excel 2002) I recently discovered that Excel can hyperlink to another cell in the same workbook, which looks to be very useful. In this re, ... Using (row,col) designation, the link's 'reference' (using the word loosly) appears to be absolute: If I set it to 'A31', and then insert/delete row 29, the link will still be to 'A31' - as if I'd spec'd $A$31, in most other contexts. I know I can get around this by referencing a 'named' target cell. This is OK, ... but, a litte more effort. So, just to be sure I'm not missing something, is there a way to specify a (row,col)-type hyperlink, so that it gets adjusted when insert/delete changes move the target cell? Thanks, George -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatical cell size adjusting | Excel Discussion (Misc queries) | |||
Adjusting cell height to greater than 5.69" | Excel Discussion (Misc queries) | |||
Adjusting row height to accomidate text in a cell | Excel Discussion (Misc queries) | |||
Adjusting a formula cell range | Excel Discussion (Misc queries) | |||
adjusting cell size in spreadsheets | Excel Discussion (Misc queries) |