Hyperlink Sort problem

[anttsmit]

my formula works well for dragging a hyperlink series to fill instead of copy
=HYPERLINK("#worksheet!"&ADDRESS(ROW(B2),COLUMN(B2)),(worksheet!B2))

until I sort and the hyperlink remains the same
=HYPERLINK("#worksheet!"&ADDRESS(ROW(B2),COLUMN(B2)),(worksheet!B6))

so the friendly_name sorts as it should but link location stays the same.

any thoughts on how to have the above formula sort is appreciated.

Thanks.

[davesexcel]

Sorting Moves the contents of the cells not the cells themselves so the references will not change.You may need to use a lookup formula.