CODEWITHSUNDEEP
excelhyperlinkreferenceindexingcell
user1083734
user1083734

Reputation: 5175

Using INDEX to reference a cell containing a HYPERLINK command

Currently I have the following set up (simplified):

enter image description here

With the formulae: enter image description here

How would I change this to make the result in A7:E7 (e.g. Google) into a hyperlink to the address stored in the result's reference cell (e.g. "www.google.com" in cell A2)?

Many thanks,

Alex

Upvotes: 0

Views: 586

Answers (1)

user3514930
user3514930

Reputation: 1717

Following the scheme :

enter image description here

Using EXCEL Function:
you can split in two columns the Hyperlink and use in the formula of Result:

=HYPERLINK(INDEX(B2:B4;D2);INDEX(A2:A4;D2))

Using VBA:
In a module add the Function:

Public Function Addr(x As Range) As String
    Addr = x.Hyperlinks.Item(1).Address
End Function

and in the Result use:

=HYPERLINK(Addr(INDEX(A6:A8;D2));INDEX(A6:A8;D2))

Using Name Manager:
In the name Manager (Under menu Formulas) add a name called "KKK", inserting:

=GET.FORMULA(INDEX(Sheet1!$A$12:$A$14;Sheet1!$D$2))

and in the result cell:

=HYPERLINK(MID(KKK;1+FIND("""";KKK);FIND(";";KKK)-(2+FIND("""";KKK)));INDEX(A12:A14;D2))

Work ONLY if it's a FORMULA, Don't work if are a Link like A6.

Upvotes: 1

Related Questions