I need to create hyperlinks from info in one tab of a workbook to a matching
cell on another tab containing more data.

Data on tab 1 will stay in the same row but is a numerical value and will
change daily. Data i want to link to in tab 2 is also a numerical value. I
need to create a hypelink that when i click on relevant cell in column A on
tab 1 it automatically takes me to the matching data in column a on tab 2 of
the sheet.

Use the =HYPERLINK() function:

=HYPERLINK("#Sheet3!I14",Sheet3!I14)

So the value of the destination will be displayed as well as a link to it.

By the way this link is both "clickable" and can be used as a number.
--
Gary''s Student - gsnu200767


"micksa" wrote:

I need to create hyperlinks from info in one tab of a workbook to a matching
cell on another tab containing more data.

Data on tab 1 will stay in the same row but is a numerical value and will
change daily. Data i want to link to in tab 2 is also a numerical value. I
need to create a hypelink that when i click on relevant cell in column A on
tab 1 it automatically takes me to the matching data in column a on tab 2 of
the sheet.

Sorry maybe didn't explain too well. I need a link so that I can click on a
cell in tab 1 and it will then jump to cell with the same number in it on tab
2. Numbers will not be in same cell on both tabs. Not the best at excel so
maybe if i give you the 2 reffrences i need.

data i will search from is in column E on tab 1. Data it relates to is in
column A on tab 2. Cell positions will be completely different so i guess a
find function needs to be included? Basically i need to click on relevant
number in column E on tab 1 and be taken to matching number in column A on
tab 2.

"Gary''s Student" wrote:

Use the =HYPERLINK() function:

=HYPERLINK("#Sheet3!I14",Sheet3!I14)

So the value of the destination will be displayed as well as a link to it.

By the way this link is both "clickable" and can be used as a number.
--
Gary''s Student - gsnu200767


"micksa" wrote:

I need to create hyperlinks from info in one tab of a workbook to a matching
cell on another tab containing more data.

Data on tab 1 will stay in the same row but is a numerical value and will
change daily. Data i want to link to in tab 2 is also a numerical value. I
need to create a hypelink that when i click on relevant cell in column A on
tab 1 it automatically takes me to the matching data in column a on tab 2 of
the sheet.

This example uses double-click. If you double-click on a cell in column E of
Sheet1, you will be transferred to a cell in column A of Sheet2 with the same
value as the cell on which you double-clicked.

If a match cannot be found, you will stay on Sheet1. This is worksheet
event code and goes in the worksheet code area:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim w As Worksheet
Set w = Sheets("Sheet2")
Set r = Range("E:E")
If Intersect(r, Target) Is Nothing Then Exit Sub
Cancel = True
v = Target.Value
With w
n = .Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
If .Cells(i, "A").Value = v Then
w.Activate
.Cells(i, "A").Select
Exit Sub
End If
Next
Sheets("Sheet1").Activate
End With
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm



--
Gary''s Student - gsnu200767


"micksa" wrote:

Sorry maybe didn't explain too well. I need a link so that I can click on a
cell in tab 1 and it will then jump to cell with the same number in it on tab
2. Numbers will not be in same cell on both tabs. Not the best at excel so
maybe if i give you the 2 reffrences i need.

data i will search from is in column E on tab 1. Data it relates to is in
column A on tab 2. Cell positions will be completely different so i guess a
find function needs to be included? Basically i need to click on relevant
number in column E on tab 1 and be taken to matching number in column A on
tab 2.

"Gary''s Student" wrote:

Use the =HYPERLINK() function:

=HYPERLINK("#Sheet3!I14",Sheet3!I14)

So the value of the destination will be displayed as well as a link to it.

By the way this link is both "clickable" and can be used as a number.
--
Gary''s Student - gsnu200767


"micksa" wrote:

I need to create hyperlinks from info in one tab of a workbook to a matching
cell on another tab containing more data.

Data on tab 1 will stay in the same row but is a numerical value and will
change daily. Data i want to link to in tab 2 is also a numerical value. I
need to create a hypelink that when i click on relevant cell in column A on
tab 1 it automatically takes me to the matching data in column a on tab 2 of
the sheet.

Gary, tried using your code (changed sheet1 name to Detail and sheet2 to
Shortages as the tab names appear on my workbook) but nothing is happening.
Also tried changing tab names to match your code but still nothing. Checked
that there is coresponding numbers on each tab too.

"Gary''s Student" wrote:

This example uses double-click. If you double-click on a cell in column E of
Sheet1, you will be transferred to a cell in column A of Sheet2 with the same
value as the cell on which you double-clicked.

If a match cannot be found, you will stay on Sheet1. This is worksheet
event code and goes in the worksheet code area:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim w As Worksheet
Set w = Sheets("Sheet2")
Set r = Range("E:E")
If Intersect(r, Target) Is Nothing Then Exit Sub
Cancel = True
v = Target.Value
With w
n = .Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
If .Cells(i, "A").Value = v Then
w.Activate
.Cells(i, "A").Select
Exit Sub
End If
Next
Sheets("Sheet1").Activate
End With
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm



--
Gary''s Student - gsnu200767


"micksa" wrote:

Sorry maybe didn't explain too well. I need a link so that I can click on a
cell in tab 1 and it will then jump to cell with the same number in it on tab
2. Numbers will not be in same cell on both tabs. Not the best at excel so
maybe if i give you the 2 reffrences i need.

data i will search from is in column E on tab 1. Data it relates to is in
column A on tab 2. Cell positions will be completely different so i guess a
find function needs to be included? Basically i need to click on relevant
number in column E on tab 1 and be taken to matching number in column A on
tab 2.

"Gary''s Student" wrote:

Use the =HYPERLINK() function:

=HYPERLINK("#Sheet3!I14",Sheet3!I14)

So the value of the destination will be displayed as well as a link to it.

By the way this link is both "clickable" and can be used as a number.
--
Gary''s Student - gsnu200767


"micksa" wrote:

I need to create hyperlinks from info in one tab of a workbook to a matching
cell on another tab containing more data.

Data on tab 1 will stay in the same row but is a numerical value and will
change daily. Data i want to link to in tab 2 is also a numerical value. I
need to create a hypelink that when i click on relevant cell in column A on
tab 1 it automatically takes me to the matching data in column a on tab 2 of
the sheet.