fixed a hyperlink

[kywenyi]

hi, i need to hyperlink my data from 1 tab to another tab in the same workbook. but i often need to insert data and my hyperlink will all run. is there anyway to fix a hyperlink to that particular data so that when i add new data on the hyperlink page, it will fix.

 

[My Aswer Is This]

Please explain in words what your ultimate goal is.

Why do you think you need to use Hyperlink.

Hyperlink links you to a range normally as far as I use it.

I do not know how to hyperlink Data

But I do know how to: If I enter Jane in Sheet(1).Range("A1") to have this same data enter into Sheet(3) Range("G12")

But I do not consider this Hyperlinking.

 

[kywenyi]

I will have a data "Jane" in tab A cell A1. when click on "Jane", it will hyperline to tab B cell A50. the reason being because we have more details for "Jane" and when click on "Jane" it will hyperlink to another tab to show more details. but often I would need to insert other names at tab B, eg i need to insert "Bob" the formula would run because i insert line in tab B. probably when i click "Jane" now it will become "Jack".

 

[My Aswer Is This]

It's always better if you provide specific details.

Your saying Tab A and Tab B

Is this sheet(1) and Sheet(2)

Or is the sheet named "A" and named "B"

And will you always be clicking on only "Jane"

What would need to happen if you Clicked on "Peter"

Where on Sheet B would we find "Peter"

And will Jane always be in Column(A) of Tab A ??


See I need to know where these names are for example Will they always be in Column(A") of both sheets?

There are 15'000 columns in Excel it would be much easier if you were to say in what column we might find Jane

And please give me the sheet names.

Do not say Tab A and Tab B

 

[kywenyi]

Hi sorry, am new to this forum.

Okay..

Sheet A - Jane (A1:A10) & (A30:A32)
Sheet B - Jane (A4)
Sheet A - Mark (A20:A15)
Sheet B - Mark (A17)

i can easily hyperlink the above but problem comes when i insert another name in between Jane and Mark

Sheet A - Jane (A1:A10) & (A30:A32)
Sheet B - Jane (A4)
Sheet A - Paul (A11:A14)
Sheet B - Paul (A8)
Sheet A - Mark (A20:A15)
Sheet B - Mark (A17)
by now my Mark in sheet B will be push down to another cell A18. i need to fix it Mark from sheet A still hyperlink to Mark in sheet B. hope this is clear. i can't post photos here. else it will be clearer.

 

[My Aswer Is This]

I do not know how to Hyperlink this way.

Normally we click on a cell and choose insert Hyperlink
You choose where you want to be Hyperlinked to.

Now there is a way to use Hyperlink using Vba but I'm not real sure how that works.

Now I can do something similar using Vba Double Click you double click on Jane in Sheet(A) and you will Be taken to Jane on Sheet(B)

You keep using this term Hyperlink.

Hyperlinks are used go to another location.

I do not believe you can Hyperlink Data


I always think users should say what their trying to do.

You have not said what your objective is.

You for some reason just keep using the term Hyperlink.

Maybe someone else here on this Forum will be able to help you.

 

[kywenyi]

I already made myself clear. I want to hyperlink from sheet A to a specific cell in Sheet B. and when adding row to Sheet B, it will still be hyperlinked to the correct data. and yes, i want to click on Jane on sheet A and go to Jane in sheet B. but you don't understand. if i insert another row above Jane in sheet B, when i click Jane from sheet A it will not go to Jane in sheet B anymore because the cell change. you understand?

 

[My Aswer Is This]

You can try this script in Sheet(1)

And it will search Sheet(2) column(A)

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
'Modified  6/10/2019  1:14:08 AM  EDT
Dim SearchString As String
Dim SearchRange As Range
SearchString = Target.TextToDisplay
Dim lastrow As Long
lastrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row
Set SearchRange = Sheets(2).Range("A1:A" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
Application.Goto SearchRange
End Sub