I'd use the worksheet_beforedoubleclick event to look for a match.

If you want to try, rightclick on the customer worksheet tab and select view
code. Paste this into the code window that just opened.

Then back to excel and double click on one of the products. If there's no
match, you'll hear a beep.

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim res As Variant
Dim myCol As Range

Set Target = Target.Cells(1)
Set myCol = Me.Parent.Worksheets("Product").Range("a:a")

Cancel = True

res = Application.Match(Target.Value, myCol, 0)
If IsError(res) Then
Beep 'no match found
Else
Application.Goto myCol(res), scroll:=True
End If

End Sub

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

gareth93 wrote:

Hey, i'm stumped!

I've got two worksheets in a workbook, and i excel to automatically
create a link so when i doubleclick on a cell on one sheet, it will
take me to the second sheet and to the correct row.

E.g.
sheet one has a list of customers down the left, and dates across the
top.
it is used to record what product each customer orders on a particular
day, by cross referencing the customer name with the date and entering
the appropriate product code in that cell.

sheet two has a list of product codes down the left, and product
information across the top (type, desc, etc.).

I need excel to automatically set up a link, so that no matter what
code is entered on the customer sheet, when i click / double click, it
will take me to the corresponding row on the product sheet.

I've been messing around with lookup tables and stuff, but i cant find
a way of doing it. Anybody???

Thanks

GoJo

--
gareth93
------------------------------------------------------------------------
gareth93's Profile: http://www.excelforum.com/member.php...o&userid=26578
View this thread: http://www.excelforum.com/showthread...hreadid=551835

--

Dave Peterson