Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Sheet1 column is marked complete, display entire row on Sheet2? | Excel Worksheet Functions | |||
formula-add 2 numbers of cell a1 and a2 of sheet1 ans-b9in sheet2 | Excel Worksheet Functions | |||
Display Rows From Sheet1 In Sheet2 (Import) | Excel Worksheet Functions | |||
Combine 3 Columns on Sheet2 to Display in 1 Column on Sheet1 | Excel Worksheet Functions | |||
can i type sheet1 A5 and make it show Sheet2 A6 cell | Excel Discussion (Misc queries) |