Advanced Filter button/Macro

[Johnyam54]

Good morning Team,

Could someone please assist me.

I'm trying to add a filter button to switch(filter) data on the same sheet.

Please see file attached.

Thanks

John

[Richard Buttrey]

Hi,

Make your Data sheet a proper database by eliminating the blank rows between quarters. Then use a Pivot Table, add a Slicer so that you can filter a particular quarter and add other Slicers if you wish for say Task, Project number etc.

[Johnyam54]

Hi Richard,

Thanks for coming back to me,

My only concern with this approach is my Qtr 0- Qtr 3 list will be very long.

Thanks

[Richard Buttrey]

Why should the length of the table and number of records matter.

A filter is a filter whether you use a Pivot Table or choose to use an Autofilter. You'll see the same number of records however you do it.

It's better that the database doesn't contain blank rows but a PT would still analyse the data.

Create a dynamic range name for the data so that the PT or indeed an Autofilter will use the whole range whatever the number of records. That will avoid the need to change the range in any manual autofilter you apply.