Link moving cells

[Guest]

I have created a personal list of contacts with two sheets: "sheet 1"with
private contacts and "sheet 2" with buissness contacts. Certain people in
"sheet 1" are hyperlinked to certain bussiness in "sheet 2" and viceversa but
as I update my contacts and keep them alphabetically I have to insert rows
and that changes the name of the cells. For example if cell A2 in "sheet 1"is
hyperlinked to cell E2 in "sheet 2" and I insert a row in "sheet 2" then cell
E2 in "sheet 2" becomes cell F2. However cell A2 in "sheet 1" remaines linked
to cell E2 in "sheet 2" which is noot the right link anymore. How can I get
arround this?

 

[Ken Johnson]

I have created a personal list of contacts with two sheets: "sheet 1"with
private contacts and "sheet 2" with buissness contacts. Certain people in
"sheet 1" are hyperlinked to certain bussiness in "sheet 2" and viceversa but
as I update my contacts and keep them alphabetically I have to insert rows
and that changes the name of the cells. For example if cell A2 in "sheet 1"is
hyperlinked to cell E2 in "sheet 2" and I insert a row in "sheet 2" then cell
E2 in "sheet 2" becomes cell F2. However cell A2 in "sheet 1" remaines linked
to cell E2 in "sheet 2" which is noot the right link anymore. How can I get
arround this?

Hi Gabi,

A partial solution is make an Defined Name for each of the companies on
Sheet 2, then when making your Sheet 1 hyperlinks, click on the
appropriate defined name.

To make an Defined Name for a company, click on the cell with the
company name then go Insert|Name|Define...|Add|OK . Do this for each
company you are wanting to hyperlink to from Sheet 1

This way you can insert rows into the companies sheet or drag a named
cell and the defined names have their "RefersTo" address altered so
that your Sheet 1 hyperlinks still work.

I say its a partial solution because the same is not true should you
sort the companies sheet. Sorting changes cell contents but does not
adjust the "RefersTo" address of Defined Names affected by the sort.

Ken Johnson