Sorting and Filter in Multiple Sheets using Hyperlinks
 

Hi,
I have an Excel Workbook containing multiple sheets.
Sheet1 contains my Customers data consisting CustID, CustName, CustAdd,
CustPhone etc.
Sheet2 contains Orders placed by customers consisting CustID, OrderID,
OrderDate etc.
I have CustID field common in both the sheets. Now I want that CustID in
Sheet1 gets hyperlinked with CustID field in Sheet2. Meaning, when I click on
CUS0005 in Sheet1, it will jumps to Sheet2 and filter out the data only for
CUS0005 in Sheet2.
I think this could be done. Plz tell me if I am making any sense.

Thanks,

--
Sheikh Saadi

Sorting and Filter in Multiple Sheets using Hyperlinks
 

You need to apply an autofilter in sheet2, the filter would be set by the
selected CustID chosen on sheet1.

To make this happen use the before-double-click event on sheet1, see example
code below
..
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
' column of the CustID assumed to be 1 (col A)
Dim sCID As String
If Target.Column = 1 Then
sCID = Trim(Target.Value)

' set filter range for sheet 2 all orders data
With Sheets("Sheet2")
.Range("A1:F500").AutoFilter Field:=1, Criteria1:=sCID
.Activate
.Range("A1").Select
End With

End If
End Sub


--

Regards,
Nigel




"Sheikh Saadi" wrote in message
...
Hi,
I have an Excel Workbook containing multiple sheets.
Sheet1 contains my Customers data consisting CustID, CustName, CustAdd,
CustPhone etc.
Sheet2 contains Orders placed by customers consisting CustID, OrderID,
OrderDate etc.
I have CustID field common in both the sheets. Now I want that CustID in
Sheet1 gets hyperlinked with CustID field in Sheet2. Meaning, when I click
on
CUS0005 in Sheet1, it will jumps to Sheet2 and filter out the data only
for
CUS0005 in Sheet2.
I think this could be done. Plz tell me if I am making any sense.

Thanks,

--
Sheikh Saadi

Sorting and Filter in Multiple Sheets using Hyperlinks
 

Thanks Nigel,
It worked... and completely fulfilled my needs. I was wrting a code which
was working (through hyperlinks) though making my worksheet mad... Your
solution is simple and without any problems... thanks a lot...


--
Sheikh Saadi


"Nigel" wrote:

You need to apply an autofilter in sheet2, the filter would be set by the
selected CustID chosen on sheet1.

To make this happen use the before-double-click event on sheet1, see example
code below
..
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
' column of the CustID assumed to be 1 (col A)
Dim sCID As String
If Target.Column = 1 Then
sCID = Trim(Target.Value)

' set filter range for sheet 2 all orders data
With Sheets("Sheet2")
.Range("A1:F500").AutoFilter Field:=1, Criteria1:=sCID
.Activate
.Range("A1").Select
End With

End If
End Sub


--

Regards,
Nigel




"Sheikh Saadi" wrote in message
...
Hi,
I have an Excel Workbook containing multiple sheets.
Sheet1 contains my Customers data consisting CustID, CustName, CustAdd,
CustPhone etc.
Sheet2 contains Orders placed by customers consisting CustID, OrderID,
OrderDate etc.
I have CustID field common in both the sheets. Now I want that CustID in
Sheet1 gets hyperlinked with CustID field in Sheet2. Meaning, when I click
on
CUS0005 in Sheet1, it will jumps to Sheet2 and filter out the data only
for
CUS0005 in Sheet2.
I think this could be done. Plz tell me if I am making any sense.

Thanks,

--
Sheikh Saadi