filter a formatted table based on value in another cell

[coffeenazi]

I have a formatted table named Table1.

I have a validated drop-down list in cell B2.

Is there a way to link the value in B2 to Table1 so that the table is filtered by column 4 to the result of the value in cell B2? For example, the user would select "Apples" from the list in B2, and Table1 would automatically be filtered to only show rows with "Apples" in column 4.

I've searched and tried numerous threads with no success so far, hoping you can help me.

thanks in advance!

 

[Aryatect]

Hi,

Can you please provide some dummy data and expected outcome for more clarity.

 

[coffeenazi]

I can't post attachments, sorry. but it would look like this.

Product:	Apples				Result: Table1 after "Apples" selected from cell B2
Month	Site	Count	Product		Month	Site	Count	Product
Jan	Site 1	10	Apples		Jan	Site 1	10	Apples
Feb	Site 2	5	Oranges		Apr	Site 4	7	Apples
Mar	Site 3	6	Pears		Jul	Site 7	4	Apples
Apr	Site 4	7	Apples		Oct	Site 10	10	Apples
May	Site 5	9	Oranges		Jan	Site 13	7	Apples
Jun	Site 6	3	Pears		Apr	Site 16	4	Apples
Jul	Site 7	4	Apples
Aug	Site 8	8	Oranges
Sep	Site 9	1	Pears
Oct	Site 10	10	Apples
Nov	Site 11	5	Oranges
Dec	Site 12	6	Pears
Jan	Site 13	7	Apples
Feb	Site 14	9	Oranges
Mar	Site 15	3	Pears
Apr	Site 16	4	Apples
May	Site 17	8	Oranges

<colgroup><col span="3"><col><col span="5"></colgroup><tbody>
</tbody>

 

[DanteAmor]

I have a formatted table named Table1.
I have a validated drop-down list in cell B2.
Is there a way to link the value in B2 to Table1 so that the table is filtered by column 4 to the result of the value in cell B2? For example, the user would select "Apples" from the list in B2, and Table1 would automatically be filtered to only show rows with "Apples" in column 4.
I've searched and tried numerous threads with no success so far, hoping you can help me.
thanks in advance!

You need something like this:

Put the following code in the events of your sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Address(0, 0) = "[COLOR=#0000ff]B2[/COLOR]" Then
        ActiveSheet.ListObjects("Table1").Range.AutoFilter [COLOR=#0000ff]Field:=4[/COLOR], Criteria1:=Target.Value
    End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[coffeenazi]

Thanks Dante, but I think that's a bit too technical for my audience. I'm hoping to find a way to run the macro on the cell change, or at the most for the user to click a button to activate it.

I need to share this workbook to a wide audience of different skill levels, so it needs to be quite foolproof.

 

[DanteAmor]

Thanks Dante, but I think that's a bit too technical for my audience. I'm hoping to find a way to run the macro on the cell change, or at the most for the user to click a button to activate it.

I need to share this workbook to a wide audience of different skill levels, so it needs to be quite foolproof.

I almost guess the real example.
If you want it to work automatically you must put the macro in the events of the sheet. Before thinking about the future, did you try my code?

You must explain other things.
Is it really a table?
Or are they just data in a range of cells?


In what row is the header?
Where do you want the results in the same table or in another part of the sheet?