Extract a list from master list
 

Hi,
I have master list (B3:C8) and I'd like to display a list (G3:G4) based on
cell F3. How I could do that?
Thank you.

Column B Column C Column F Column G
Row 3 Fruit Apple Vegetable Cabbage
Row 4 Vegetable Cabbage Broccoli
Row 5 Fruit Grape Spinach
Row 6 Vegetable Broccoli
Row 7 Vegetable Spinach
Row 8 Fruit Orange

Extract a list from master list
 

Try this event macro:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$2" Then
Application.EnableEvents = False
Application.ScreenUpdating = False
columnheaderG = Range("G1").Value
Range("A:B").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=Range("F2").Value
Selection.CurrentRegion.Select
Range("B:B").SpecialCells(xlCellTypeVisible).Selec t
Range("G:G").ClearContents
Selection.Copy Destination:=Range("G1")
Selection.AutoFilter
Range("G1").Value = columnheaderG
Range("F2").Select
Application.ScreenUpdating = True
Application.EnableEvents = True
End If
End Sub

Additionally you can create a Data Validation list for F2.

Regards,
Stefi


€žjs€� ezt Ã*rta:

Hi,
I have master list (B3:C8) and I'd like to display a list (G3:G4) based on
cell F3. How I could do that?
Thank you.

Column B Column C Column F Column G
Row 3 Fruit Apple Vegetable Cabbage
Row 4 Vegetable Cabbage Broccoli
Row 5 Fruit Grape Spinach
Row 6 Vegetable Broccoli
Row 7 Vegetable Spinach
Row 8 Fruit Orange

Extract a list from master list
 

In column A write this formula:
=IF(B3=$f$3, COUNTIF(B$3:B3, B3), "") drag this formula down.
In column H make a list of 1, 2, 3, 4,............. and so on
In column G type formula:
=IF(ISERROR(VLOOKUP(H3, $A$3:$C$100, 3, 0)), "", VLOOKUP(H3, $A$3:$C$100, 3,
0))
Hide column A and Column H.
this trick can do your work.

"js" wrote:

Hi,
I have master list (B3:C8) and I'd like to display a list (G3:G4) based on
cell F3. How I could do that?
Thank you.

Column B Column C Column F Column G
Row 3 Fruit Apple Vegetable Cabbage
Row 4 Vegetable Cabbage Broccoli
Row 5 Fruit Grape Spinach
Row 6 Vegetable Broccoli
Row 7 Vegetable Spinach
Row 8 Fruit Orange

Extract a list from master list
 

Another play ..

In G3:
=IF(ROWS($1:1)COUNT(H:H),"",INDEX(C:C,SMALL(H:H,R OWS($1:1))))

In H3:
=IF(B3="","",IF(B3=F$3,ROW(),""))
Leave H1:H2 blank

Select G3:H3, copy down to H8, or as far down as required to cover the max
expected extent of source data. Hide away col H. That'll give you the desired
results depending on the input in F3.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"js" wrote:
Hi,
I have master list (B3:C8) and I'd like to display a list (G3:G4) based on
cell F3. How I could do that?
Thank you.

Column B Column C Column F Column G
Row 3 Fruit Apple Vegetable Cabbage
Row 4 Vegetable Cabbage Broccoli
Row 5 Fruit Grape Spinach
Row 6 Vegetable Broccoli
Row 7 Vegetable Spinach
Row 8 Fruit Orange

Extract a list from master list
 

In column A write this formula:
=IF(B3=$f$3, COUNTIF(B$3:B3, B3), "") drag this formula down.
In column G type formula:
=IF(ISERROR(VLOOKUP(row(1:1), $A$3:$C$100, 3, 0)), "", VLOOKUP(row(1:1),
$A$3:$C$100, 3, 0))
Hide column A.
Let us know if it works.

"Excel_Learner" wrote:

In column A write this formula:
=IF(B3=$f$3, COUNTIF(B$3:B3, B3), "") drag this formula down.
In column H make a list of 1, 2, 3, 4,............. and so on
In column G type formula:
=IF(ISERROR(VLOOKUP(H3, $A$3:$C$100, 3, 0)), "", VLOOKUP(H3, $A$3:$C$100, 3,
0))
Hide column A and Column H.
this trick can do your work.

"js" wrote:

Hi,
I have master list (B3:C8) and I'd like to display a list (G3:G4) based on
cell F3. How I could do that?
Thank you.

Column B Column C Column F Column G
Row 3 Fruit Apple Vegetable Cabbage
Row 4 Vegetable Cabbage Broccoli
Row 5 Fruit Grape Spinach
Row 6 Vegetable Broccoli
Row 7 Vegetable Spinach
Row 8 Fruit Orange