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.

[TABLE="width: 582"]
<colgroup><col span="3"><col><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product:[/TD]
[TD]Apples[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]Result: Table1 after "Apples" selected from cell B2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Month[/TD]
[TD]Site[/TD]
[TD]Count[/TD]
[TD]Product[/TD]
[TD][/TD]
[TD]Month[/TD]
[TD]Site[/TD]
[TD]Count[/TD]
[TD]Product[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Site 1[/TD]
[TD]10[/TD]
[TD]Apples[/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD]Site 1[/TD]
[TD]10[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]Site 2[/TD]
[TD]5[/TD]
[TD]Oranges[/TD]
[TD][/TD]
[TD]Apr[/TD]
[TD]Site 4[/TD]
[TD]7[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD]Site 3[/TD]
[TD]6[/TD]
[TD]Pears[/TD]
[TD][/TD]
[TD]Jul[/TD]
[TD]Site 7[/TD]
[TD]4[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]Apr[/TD]
[TD]Site 4[/TD]
[TD]7[/TD]
[TD]Apples[/TD]
[TD][/TD]
[TD]Oct[/TD]
[TD]Site 10[/TD]
[TD]10[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD]Site 5[/TD]
[TD]9[/TD]
[TD]Oranges[/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD]Site 13[/TD]
[TD]7[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]Jun[/TD]
[TD]Site 6[/TD]
[TD]3[/TD]
[TD]Pears[/TD]
[TD][/TD]
[TD]Apr[/TD]
[TD]Site 16[/TD]
[TD]4[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]Site 7[/TD]
[TD]4[/TD]
[TD]Apples[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aug[/TD]
[TD]Site 8[/TD]
[TD]8[/TD]
[TD]Oranges[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sep[/TD]
[TD]Site 9[/TD]
[TD]1[/TD]
[TD]Pears[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oct[/TD]
[TD]Site 10[/TD]
[TD]10[/TD]
[TD]Apples[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nov[/TD]
[TD]Site 11[/TD]
[TD]5[/TD]
[TD]Oranges[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dec[/TD]
[TD]Site 12[/TD]
[TD]6[/TD]
[TD]Pears[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Site 13[/TD]
[TD]7[/TD]
[TD]Apples[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]Site 14[/TD]
[TD]9[/TD]
[TD]Oranges[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD]Site 15[/TD]
[TD]3[/TD]
[TD]Pears[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apr[/TD]
[TD]Site 16[/TD]
[TD]4[/TD]
[TD]Apples[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD]Site 17[/TD]
[TD]8[/TD]
[TD]Oranges[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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?