Click one cell sheet1, display all related recs on sheet2
 

I have a situation whereby I have one set of records on sheet1 (1 rec per
row) with a different set of records on sheet2. While there is only one
record per client (1 row) on sheet1, there are one or more associated records
on sheet2.
I want to allow the user to click on the key field cell for a record on
sheet1 that will open sheet2 and display only those records (1 or more rows)
with a corresponding key value.
EXAMPLE:

SHEET1 SHEET2
1 1
2 2
3 2
4 2
5 2
3
3
4
5
5
The user clicks "2" on sheet1
This opens sheet2
sheet2 displays those rows with "2" in the key field (4 rows)

Can anyone help me?

Click one cell sheet1, display all related recs on sheet2
 

Hi

The macro below should do it:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set isect = Intersect(Target, Range("A2:A7"))
If Not isect Is Nothing Then
With Sheets("Sheet2")
.Range("A1:A1000").AutoFilter FIeld:=1,
Criteria1:=Target.Value
End With
Sheets("Sheet2").Activate
End If
End Sub

Right click on the tab for sheet1 and select "wiew code" and insert
the code into the codesheet. Change the ranges as desired. Close the
VBA editor and test it.

Regards,
Per


On 17 Dec., 23:29, Ginsu wrote:
I have a situation whereby I have one set of records on sheet1 (1 rec per
row) with a different set of records on sheet2. While there is only one
record per client (1 row) on sheet1, there are one or more associated records
on sheet2.
I want to allow the user to click on the key field cell for a record on
sheet1 that will open sheet2 and display only those records (1 or more rows)
with a corresponding key value.
EXAMPLE:

SHEET1 * * * * * * *SHEET2
1 * * * * * * * * * * * *1
2 * * * * * * * * * * * *2
3 * * * * * * * * * * * *2
4 * * * * * * * * * * * *2
5 * * * * * * * * * * * *2
* * * * * * * * * * * * * 3
* * * * * * * * * * * * * 3
* * * * * * * * * * * * * 4
* * * * * * * * * * * * * 5
* * * * * * * * * * * * * 5
The user clicks "2" on sheet1
This opens sheet2
sheet2 displays those rows with "2" in the key field *(4 rows)

Can anyone help me?