Getting a hyperlink to follow a sort

[Guest]

Worksheet 1 is a table to list up to 100 prospective customers. Worksheets
2-101 are prospective customer profile forms. Each name cell on Worksheet 1
is hpyerlinked to a unique profile. The problem is when I re-sort the list
of names on worksheet 1, the hyperlinks remain with the cells and do not
"follow" the new location of the name caused by the sort. How can I get a
hyperlink (or some alternative) to stay with the row as it gets resorted?

 

[Dave Peterson]

I'd drop the insert|hyperlink version of the hyperlinks and use a worksheet
formula in an adjacent cell:

If your worksheet name were in A2, you could put:

=HYPERLINK("#"&CELL("address",indirect("'" & a2 & "'!A1")),"Clickme")

Then make sure you include both columns when you sort.

 

[Guest]

Thanks, David - I got your suggestion to work - but the cell containing
"clickme" takes up 2 rows which will mess things up when I sort. Any way to
have it take up only 1? Also, when I try to sort, I get an error message:
"This operation requires the merged cells to be identically sized".

 

[Dave Peterson]

The =hyperlink() formula doesn't need two cells.

I'm guessing you had a couple of merged cells where you typed the formula.

Just select the range and remove the merged cells:
format|cells|alignment tab

(I try to stay away from merged cells for reasons like this.)

 

[Guest]

Dave,

This seems like it would solve the same problem I am having. The only
difference is that I have the hyperlink in column A which links to a
different file in a different location. The hyperlink has path coded into it
with a reference to column B of the same row for the file name. There are
over 14,000 rows which need to be sorted and have the hyperlink point to the
correct file instead of the original.

For example, I currently have the hyperlink in cell A3 as follows (SCAN2 is
the file name):

=HYPERLINK("\\network\documents\drawings\scans\all\"&SCAN2!B3&".tif","View
Scan")

How would I adjust your hyperlink formula to use it with the path coded and
the reference to the cell in the same row in column B for the file name?

Thank you very much.

 

[Dave Peterson]

I'm not sure I understand, but something like:

in A3:
=hyperlink("file:////" & b3, "view scan")

if b1 contained both path and filename.

Or if your filenames are always prefixed with Scan, but that's not part of the
value in B3

=HYPERLINK("\\network\documents\drawings\scans\all\SCAN2" & B3 &".tif",
"View Scan")

I've always included the "file:////" stuff, though.

If you can't get it to work, maybe you could give some more info.

 

[Guest]

Dave,

I had the filename prefixed with 'Scan' when I had the paths on a different
sheet and never took it off when I put the path directly in the hyperlink.
Once I took the 'Scan' off, the sorting working properly.

Thank you.

 

[Dave Peterson]

Glad you got it working.

Dave,

I had the filename prefixed with 'Scan' when I had the paths on a different
sheet and never took it off when I put the path directly in the hyperlink.
Once I took the 'Scan' off, the sorting working properly.

Thank you.