Problem with creating a hyperlink function

[cplettner]

Hi,

I'm trying to hyperlink to a cell in another sheet in the same workbook. I can get the furmula to work, but if I add rows above the targe cell then they Hyperlink formula doesn't automatically adjust. How do I correct for this Below is the example.

Sheet1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Address[/TD]
[TD]Painting[/TD]
[TD]Cleaning[/TD]
[/TR]
[TR]
[TD]320 Knox[/TD]
[TD]Best Painting[/TD]
[TD]A+ Cleaning[/TD]
[/TR]
[TR]
[TD]another address[/TD]
[TD]Best Painting[/TD]
[TD]A+ Cleaning[/TD]
[/TR]
</tbody>[/TABLE]

"Vendor" Sheet

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Company Name[/TD]
[TD]Contact Person[/TD]
[TD]Phone #[/TD]
[/TR]
[TR]
[TD]Painting[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Best Painting[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cleaning[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A+ Cleaning[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want the B2 cell in "Sheet 1" to hyperlink to the B3 cell in "Vendors" I am using the following formula which works

=HYPERLINK("#Vendors!b3",Vendors!$B$3)

However, if I go into the Vendor sheet and say add a row above the Best Painting the formula changes to the following:

=HYPERLINK("#Vendors!b3",Vendors!$B$4)

As you can see the link location remains at b3 which is no longer where the information is, but the friendly name changes to the correct cel reference (one cell down).

How can I fix this?

Thanks