fixed a hyperlink

kywenyi

New Member
Joined
May 11, 2019
Messages
13
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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.
 
Upvote 0
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".
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top