CODEWITHSUNDEEP
excelhyperlinkexcel-formulaexcel-2010vba
Sage Mohan
Sage Mohan

Reputation: 155

Hyperlinked cell to to show results on a different worksheet

I have two worksheets, Sheet1 and Sheet2. Sheet1 has a list like shown below:

 Col1
 A1A
 A2A
 A2C
 A4A
 A4B

and sheet2 has a formula on cell b2 that does an index and match function. Am fairly new to excel and was wondering can I make the Col1 in Sheet1 into hyperlinks so that anytime I click on an item on Col1 (lets say A1A), it takes me to Sheet2 and the Cell B2 on sheet2 gets populated with the clicked hyperlink?

Upvotes: 0

Views: 65

Answers (3)

Absinthe
Absinthe

Reputation: 3391

Use the Follow_Hyperlink event which has range called Target as a parameter, which is the cell containing the hyperlink. Use the range to set up your logic e.g:

If Not Application.Intersect(Target, Range("B4")) Is Nothing Then
     Sheets("Sheet2").Activate
     Sheets("Sheet2").Range(Target.Address) = whatever you want in B4 on sheet 2
End if

Upvotes: 0

PeterT
PeterT

Reputation: 8557

With acknowledgement to the AbleBits site, make sure you note a couple things in the process.

For this example I want cell C3 on Sheet2 to point back to cell A1 on Sheet1. So, I have this formula in Sheet2!C3:

enter image description here

Then on Sheet1, in Cell A1, I right-clicked to pull up the menu then selected Hyperlink.... Make sure of three things:

  1. Choose Place in This Document
  2. Choose the correct sheet to reference (Sheet2)
  3. Choose the correct cell to refernece (cell C3)
  4. Optionally change the Text to display at the top of the dialog.

enter image description here

With all that, you get the following on Sheet1:

enter image description here

So when you click the link in cell A1 on Sheet1, you get sent back to Sheet2 and it shows:

enter image description here

Upvotes: 0

John Muggins
John Muggins

Reputation: 1198

have you tried a sheet module selection change? ie.,

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
    If Target.Value = "A1A" Or Target.Row = 1 Then
        Sheet2.Range("B" & Target.Row) = Target.Value
    End If
End If
End Sub

Upvotes: 1

Related Questions