If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|