EXCEL maintain hyperlink with the cell content after sorting/ using formula

Question

INTRODUCTION I have made a excel sheet of file index. Each file entry is linked with scanned copy of the file. So when i click the file name in excel sheet the linked PDF file opens.

ACTIVITY In a separate worksheet i have made a search function based on multiple search results with partial matching. So when i enter part of either file name or subject i get the file name using

" =IF($F$1="","",IFERROR(INDEX(A$2:A$8,SMALL(IF(ISNUMBER(SEARCH($F$1,B$2:B$8)),ROW(B$2:B$8)-ROW(B$2)+1),ROWS(B$2:B2))),"")) "

PROBLEM The file name obtained is plain text without the hyperlink associated with the file name in the master database.

WHAT HAS BEEN TRIED I have tried using (A) =IFERROR(HYPERLINK(.....)) (B) =HYPERLINK(CELL NUMBER)(By getting the row number from formula and using index function to reference the cell)

Answer 1

There is a tiny trick:

Insure the hyperlinks in the source column are formatted so that the "display name" is the URL. You can use either Inserted hyperlinks or the =HYPERLINK() worksheet function.

Say we have links in column A and we want an alphbetized list in column C. In B1 enter:

=LOOKUP(1,0/FREQUENCY(ROWS($1:1),COUNTIF($A$1:$A$3,"<="&$A$1:$A$3)),$A$1:$A$3)

and copy downward. (Column B is in alphabetic order, but the links are "cold") Then in C1 enter:

=HYPERLINK(B1,B1)

Even though the links in column B are "cold", the links in column C are "hot"