automatic links from onwe worksheet to another, depending on text

G

gareth93

Hey, i'm stumped! :mad:

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
 
D

Dave Peterson

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
 
G

gareth93

yea, that works well. thanks very much. is there any way to search for
multiple product codes in the same cell? excel views multiple values as
one value and searches for it on my list. is there any way to double
click on a particular word in a cell. The way the referencing works i'm
guessing not, but i would like to try.
 
D

Dave Peterson

Maybe you could split it up -- based on a comma separator or space separator?

But you'll doubleclick on the cell--not a word in the cell.

Maybe it's time to bite the bullet and learn about userforms. If there's more
than one product code in the cell, you could show a userform.

In that userform, you could display all the product codes in a combobox and let
the user choose from that list?

If you want to look at these pages from Debra Dalgleish's site:
http://www.contextures.com/xlUserForm01.html
and
http://www.contextures.com/xlUserForm02.html
 
Top