G
Guest
How can I retain the hyperlink target cell when adding or deleting rows in
the target sheet?
the target sheet?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
David McRitchie
Please describe what it is that you want to happen, or what is happening that
you don't want to happen with an exact description of how to reproduce you
problem. There are object hyperlinks, and there is HYPERLINK Worksheet Function.
If you delete the row that hyperlink pointed to you are going to get an error,
so what would you want to happen instead.
you don't want to happen with an exact description of how to reproduce you
problem. There are object hyperlinks, and there is HYPERLINK Worksheet Function.
If you delete the row that hyperlink pointed to you are going to get an error,
so what would you want to happen instead.
G
Guest
I apologize in advance for my minimal computer knowledge... here is an
example of my problem: I have created a hyperlink from Sheet 1 Cell 1A to
Sheet 2 Cell 3A. If if I add a row above my Sheet 2 Row 3, that hyperlink
will go to the new Sheet 2 Cell 3A. I want for the target of the hyperlink
to move to what is now Sheet 2 Cell 4A, the cell containing the original
information.
I hope this makes some sense. I'm sorry I cannot explain this in better
terms. I appreciate your assistance.
example of my problem: I have created a hyperlink from Sheet 1 Cell 1A to
Sheet 2 Cell 3A. If if I add a row above my Sheet 2 Row 3, that hyperlink
will go to the new Sheet 2 Cell 3A. I want for the target of the hyperlink
to move to what is now Sheet 2 Cell 4A, the cell containing the original
information.
I hope this makes some sense. I'm sorry I cannot explain this in better
terms. I appreciate your assistance.
D
David McRitchie
Hi AGK,
What you indicate would only happen with HYPERLINK Worksheet Formulas
with the link within quotes. Anything within quotes will not change. If that is the
case then why didn't you supply the formula. So it might be something else, but
whatever that might be -- you are not describing it. And there is no cell 3A, such
a cell would be referenced as A3. When it comes to functions and macros that
refer to rows and columns as separate parameters the row does come before
the column parameter within builtin functions because that is the way storage is
laid out or at least perceived. .
Sheet1 Cell A1 has formula
=HYPERLINK("sheet2!A3",sheet2!A3)
then the hyperlink address is in double quotes and will not change, but the
value shown will be from the new location.
The following will solve that problem.
=HYPERLINK("#"&CELL("address",sheet2!A3),sheet2!A3)
see Hyperlink Worksheet References outside of quotes (#hyperlink_ref)
http://www.mvps.org/dmcritchie/excel/sheets.htm#hyperlink_ref
Had then been object hyperlinks -- Ctrl+K then I don't think you would have
the problem you describe. Besides the above page another page on
hyperlinks is http://www.mvps.org/dmcritchie/excel/buildtoc.htm.
If you don't mind, it sure would be nicer if you used your real name when
posting.
HTH,
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
What you indicate would only happen with HYPERLINK Worksheet Formulas
with the link within quotes. Anything within quotes will not change. If that is the
case then why didn't you supply the formula. So it might be something else, but
whatever that might be -- you are not describing it. And there is no cell 3A, such
a cell would be referenced as A3. When it comes to functions and macros that
refer to rows and columns as separate parameters the row does come before
the column parameter within builtin functions because that is the way storage is
laid out or at least perceived. .
Sheet1 Cell A1 has formula
=HYPERLINK("sheet2!A3",sheet2!A3)
then the hyperlink address is in double quotes and will not change, but the
value shown will be from the new location.
The following will solve that problem.
=HYPERLINK("#"&CELL("address",sheet2!A3),sheet2!A3)
see Hyperlink Worksheet References outside of quotes (#hyperlink_ref)
http://www.mvps.org/dmcritchie/excel/sheets.htm#hyperlink_ref
Had then been object hyperlinks -- Ctrl+K then I don't think you would have
the problem you describe. Besides the above page another page on
hyperlinks is http://www.mvps.org/dmcritchie/excel/buildtoc.htm.
If you don't mind, it sure would be nicer if you used your real name when
posting.
HTH,
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