Hyperlink doesn't work with INDEX/MATCH function as friendly name

[Ferty]

Hello,
I found problem with my HYPERLINK and really couldn't find solution for that.

I have workbook with few Sheets, one of them is my source data which includes hyperlinks with friendly names and without.
I have problem when i try use one of data with hyperlink covered by friendly name.

I extract the link from hyperlink with function:

Function GetUrl(WorkingRng As Range) As String

GetUrl = WorkingRng.Hyperlinks(1).Address

End Function

and my function looks like this:
=HYPERLINK(GetUrl(INDEX(OnlySource,ROW()-1,MATCH(L$2,Lnk,0))),INDEX(OnlySource,ROW()-1,MATCH(L$2,Lnk,0)))

and this doesn't work.

When i change friendly name part:

INDEX(OnlySource,ROW()-1,MATCH(L$2,Lnk,0))

to :
"SOME TEXT"

it works.

When i use only:

=HYPERLINK(GetUrl(INDEX(OnlySource,ROW()-1,MATCH(L$2,Lnk,0))))

it works, but i need friendly name for it
which i can get from:

INDEX(OnlySource,ROW()-1,MATCH(L$2,Lnk,0))

OnlySource is dynamically changing range depends whats data is imported

=OFFSET('1st PASTE AIMS SOURCE'!$A$2,0,0,COUNTA('1st PASTE AIMS SOURCE'!$A:$A),COUNTA('1st PASTE AIMS SOURCE'!$2:$2)-1)

Lnk is dynamically changing range for headers depends whats data is imported

=OFFSET('1st PASTE AIMS SOURCE'!$A$2,0,0,1,COUNTA('1st PASTE AIMS SOURCE'!$2:$2)-1)

Please help with some solution to be able have working hyperlink with valid friendly name which is taken from same cell.

 

[Legacy 456129]

Your 'Friendly' name has not been accidentally limited to the particular worksheet when you defined it on Name Manager? If so, you need to give it a 'worksheet'! path if accessing from another worksheet.

 

[Ferty]

Your 'Friendly' name has not been accidentally limited to the particular worksheet when you defined it on Name Manager? If so, you need to give it a 'worksheet'! path if accessing from another worksheet.

Hey SekeRob2,
In dynamic range i have path to worksheet

'1st PASTE AIMS SOURCE'

Or should be directly written without dynamic range , but then i would have problem because each import is different and even column with same name can be in different columns that's why i use index/match functions to find proper column and then find proper row.

Cells from which i gain data looks like this:

Example

and inside i have hyperlink to some page:

http://example.com

When i want use index/match functions to get this cell in my target worksheet from source worksheet:

=INDEX(OnlySource,ROW()-1,MATCH(L$2,Lnk,0))

i will get result:

Example

without hyperlink.
When i will do function with hyperlink function:

=HYPERLINK(INDEX(OnlySource,ROW()-1,MATCH(L$2,Lnk,0)))

the my result will be:

Example

but hyperlink leads to the page:

Example

which doesn't exists.
So i use function GetUrl which i defined earlier and when i use this function with hyperlink:

=HYPERLINK(GetUrl(INDEX(OnlySource,ROW()-1,MATCH(L$2,Lnk,0))))

Then my result will be:

http://example.com

and this will lead to my page i want, but i need here friendly name.
So i use function from to have working hyperlink and add only function for valid friendly name

=HYPERLINK(GetUrl(INDEX(OnlySource,ROW()-1,MATCH(L$2,Lnk,0))),INDEX(OnlySource,ROW()-1,MATCH(L$2,Lnk,0)))

and result is:

Example

But even i click on cell i have no message or anything that's should looks like this:

So i don't know what's going on.

 

[Ferty]

Hello,
I don't know how to attach file and don't have dropbox so i make image to show my problem more understandable.
I want make cell A2 to work (open page which is thereand I don't understand why.

 

[Ferty]

Hey,
sorry something cut my sentence and don't see edit button to fix last post.

I want make cell A2 to work to open gmail.com link but, it does nothing and I don't understand why.
"F2" cell works perfectly for friendly name and somehow when we add this function to "A2" as friendly name argument it's making all function in "A2" cell stop to work and nothing open or give any message.

If someone had similar problem please help i just stopped my project because of this annoying issue.

Thank you in advance.