Forum Discussion

riaanl007's avatar
riaanl007
Copper Contributor
Aug 04, 2021

Help wiht excel auto filter

Hi,
I have a problem in Excel that I need some assistance with please. I will try to explain to the best of my ability.

I have a spreadsheet with multiple tabs. Let’s say Tab 1 is my “information” tab where I want to manage and activate my filters from. Tab 2 is the tab where all the data is in with multiple rows and columns. Here I can easily filter information in row A to what I need.

Now, what I want to do is create a “Trigger” in Tab 1, so that when I click on it, it automatically filters Tab 2 to the column selected. Example, Tab 2 row a = Birds. On tab 1 if I click Birds it must take me to Tab 2 and filter accordingly. Then there must be a reset button to rest to default view.

Hope this makes sense.
 
excel

4 Replies

  • RockstarExcel's avatar
    RockstarExcel
    Copper Contributor
    Aug 05, 2021

    riaanl007 

     

    I see two ways to do this.

     

    First, you could record a quick macro where you filter to what you need, and make that into a button.

     

    If you don't want to use macros, I'd do this with three tabs.

     

    In Tab 1, use Data Validation to create a dropdown list of the values you want to filter to.

     

    In Tab 3, have all of your data.

     

    Then in Tab 2, use the FILTER() function, filtering to values that are equal to what you select in the dropdown in Tab 1.

     

    =FILTER('Sheet3'!A:E,'Sheet3'!A:A=Sheet1!$A$2)

     

    Then anytime you change the selection on your dropdown in Tab 1, your filtered data in Tab 2 will update.

     

    See attached example.

    • riaanl007's avatar
      riaanl007
      Copper Contributor
      Aug 05, 2021

      Hi RockstarExcel 

       

      Thanks for the feedback.

       

      what I am looking for on your sheet 1 is not a dropdown, but a button, so that wen I click it, it takes me to sheet 2, automatically filters sheet 2 to only show the information I clicked on. ~So if I click on Clothing for example on sheet 1, on sheet 2 it auto filters only clothing

      • RockstarExcel's avatar
        RockstarExcel
        Copper Contributor
        Aug 05, 2021

        riaanl007 

         

        In that case you'll want to record a macro.

         

        Here's an article that walks you through recording a macro, without having to do any VBA programming.

         

        https://spreadsheeto.com/make-a-macro/

         

        Hope that helps.

Resources