I use function "=SUMPRODUCT((Sheet2!A2:A10="Food")*(Sheet2!C2:C10 ))" to
get the total cost in sheet1. Do anyone know how to implement the
following function: When I click the cell with value of $42.00 in the
total cost column of Shee1. The corresponding rows with "Food" type in
Sheet2 will be highlighted or retrieved out. Thanks a lot!!!

------------------
Sheet1
ID TYPE TOTAL COST
1 Food $42.00
2 Drink $18.90
3 Movie $21.10
4 Music $64.99
------------------------
Sheet2
TYPE DETAIL COST
Drink Coke $3.50
Movie DVD Rent $9.10
Movie Tickets $12.00
Food Seafood $25.00
Drink Wine $15.40
Music Tickets $55.00
Food Hamburg $5.00
Food Cheese $12.00
Music CD $9.99


--
minrufeng
------------------------------------------------------------------------
minrufeng's Profile: http://www.excelforum.com/member.php...o&userid=26208
View this thread: http://www.excelforum.com/showthread...hreadid=395172

One way:

Use a selection change event on sheet1 to autofilter sheet2.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo Errorhandler
Application.ScreenUpdating = False
Application.EnableEvents = False
If Target.Column = 3 And Target.HasFormula Then
Sheets("Sheet2").Range("A1").AutoFilter _
field:=1, Criteria1:=Target.Offset(0, -1).Value
End If
Errorhandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Right click the sheet tab for sheet1, select view code and paste the event
code above.

Hope this helps
Rowan

"minrufeng" wrote:


I use function "=SUMPRODUCT((Sheet2!A2:A10="Food")*(Sheet2!C2:C10 ))" to
get the total cost in sheet1. Do anyone know how to implement the
following function: When I click the cell with value of $42.00 in the
total cost column of Shee1. The corresponding rows with "Food" type in
Sheet2 will be highlighted or retrieved out. Thanks a lot!!!

------------------
Sheet1
ID TYPE TOTAL COST
1 Food $42.00
2 Drink $18.90
3 Movie $21.10
4 Music $64.99
------------------------
Sheet2
TYPE DETAIL COST
Drink Coke $3.50
Movie DVD Rent $9.10
Movie Tickets $12.00
Food Seafood $25.00
Drink Wine $15.40
Music Tickets $55.00
Food Hamburg $5.00
Food Cheese $12.00
Music CD $9.99


--
minrufeng
------------------------------------------------------------------------
minrufeng's Profile: http://www.excelforum.com/member.php...o&userid=26208
View this thread: http://www.excelforum.com/showthread...hreadid=395172

Thanks a lot. It works. However, I want to keep my original sheet, n
hidden rows, no drop down list on it. Could you put the detail result
in a new sheet?

Thanks again. I appreicate your great help

--
minrufen
-----------------------------------------------------------------------
minrufeng's Profile: http://www.excelforum.com/member.php...fo&userid=2620
View this thread: http://www.excelforum.com/showthread.php?threadid=39517