Conditional Hyperlink if Value from Sheet1 Matches Value in Sheet2-CodePudding

I would like to create a conditional hyperlink in Column A (Link) of Sheet 1 to a matched cell in Sheet 2 when a value in Column B (Number) of Sheet 1 matches that of Column A in Sheet 2 (Number).

Sheet 1 Sheet 2

In the first instance, I don't even seem to be able to create a hyperlink between 2 sheets... Sheet 1 hyperlinked to Sheet 2 using hyperlink function in function library

I also tried using the MATCH functions to at least confirm if I could accurately reference matching cell in a different worksheet and it seems to work; but just can't seem to (1) get hyperlink between sheets to work and (2) create hyperlinks conditionally to when there are matching values. enter image description here

Unsure if I may need to use INDEX as well or Macros; just a bit lost on how to achiever my goal overall and would appreciate any tips from the community <3

CodePudding user response：

In formula way, you can try function =CELL("address",INDEX(MATCH))) to return matched cell's address, then create link to it.

One more note, if you want to create link within your workbook, use '#" before cell's address.

=HYPERLINK("#"&CELL("address",INDEX(Sheet2!$A$2:$A$6,MATCH(B2,Sheet2!$A$2:$A$6,0))),B2)

Sheet1:

CodePudding user response：

or Macro way:

Sub FindnLink()
Dim a As String
Set source_range = ThisWorkbook.Sheets("Sheet1").Range("B2:" & Range("B2").End(xlDown).Address)
Set search_range = ThisWorkbook.Sheets("Sheet2").Range("A2:" & Range("A2").End(xlDown).Address)

For Each cell In source_range
   cell.Offset(0, -1).Select
   a = cell.Value
    Excel.ThisWorkbook.Sheets("Sheet1").Hyperlinks.Add Anchor:=Selection, Address:="", _
    SubAddress:="Sheet2!" & search_range.Find(What:=cell.Value, After:=ActiveCell, LookIn:=xlFormulas2, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Address, _
    TextToDisplay:=a
Next cell

End Sub