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

up vote
0
down vote

favorite

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)

asked Nov 19 at 15:27

Nilesh Athare

add a comment |

up vote
0
down vote

favorite

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)

asked Nov 19 at 15:27

Nilesh Athare

add a comment |

up vote
0
down vote

favorite

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)

asked Nov 19 at 15:27

Nilesh Athare

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)

excel hyperlink

asked Nov 19 at 15:27

Nilesh Athare

asked Nov 19 at 15:27

Nilesh Athare

asked Nov 19 at 15:27

Nilesh Athare

asked Nov 19 at 15:27

Nilesh Athare

asked Nov 19 at 15:27

Nilesh Athare

add a comment |

1 Answer
1

active

oldest

votes

up vote
0
down vote

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)

enter image description here

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

edited Nov 19 at 16:21

answered Nov 19 at 15:53

Gary's Student

71.8k93561

Thanx a ton,, have upvoted but doesnt show here..a great trick..
– Nilesh Athare
Nov 19 at 17:28

@NileshAthare Thanks for the feedback!
– Gary's Student
Nov 19 at 17:43

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53377821%2fexcel-maintain-hyperlink-with-the-cell-content-after-sorting-using-formula%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
0
down vote

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)

enter image description here

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

edited Nov 19 at 16:21

answered Nov 19 at 15:53

Gary's Student

71.8k93561

Thanx a ton,, have upvoted but doesnt show here..a great trick..
– Nilesh Athare
Nov 19 at 17:28

@NileshAthare Thanks for the feedback!
– Gary's Student
Nov 19 at 17:43

add a comment |

up vote
0
down vote

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)

enter image description here

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

edited Nov 19 at 16:21

answered Nov 19 at 15:53

Gary's Student

71.8k93561

Thanx a ton,, have upvoted but doesnt show here..a great trick..
– Nilesh Athare
Nov 19 at 17:28

@NileshAthare Thanks for the feedback!
– Gary's Student
Nov 19 at 17:43

add a comment |

up vote
0
down vote

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)

enter image description here

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

edited Nov 19 at 16:21

answered Nov 19 at 15:53

Gary's Student

71.8k93561

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)

enter image description here

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

edited Nov 19 at 16:21

answered Nov 19 at 15:53

Gary's Student

71.8k93561

edited Nov 19 at 16:21

answered Nov 19 at 15:53

Gary's Student

71.8k93561

answered Nov 19 at 15:53

Gary's Student

71.8k93561

answered Nov 19 at 15:53

Gary's Student

71.8k93561

Thanx a ton,, have upvoted but doesnt show here..a great trick..
– Nilesh Athare
Nov 19 at 17:28

@NileshAthare Thanks for the feedback!
– Gary's Student
Nov 19 at 17:43

add a comment |

Thanx a ton,, have upvoted but doesnt show here..a great trick..
– Nilesh Athare
Nov 19 at 17:28

@NileshAthare Thanks for the feedback!
– Gary's Student
Nov 19 at 17:43

Thanx a ton,, have upvoted but doesnt show here..a great trick..
– Nilesh Athare
Nov 19 at 17:28

@NileshAthare Thanks for the feedback!
– Gary's Student
Nov 19 at 17:43

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Nsryjdtyk