Forum Discussion

Vince_Lunetta_1962

Copper Contributor

Jul 20, 2021

Advanced filter by DATE

Does Advanced filter allow for DATES by Month only. I'd like to enter a month in a cell and auto filter the table for all data points with the month regardless of year. This is the table: ...

excel

Vince_Lunetta_1962

Copper Contributor

Jul 20, 2021

Thx Sergei. Still will not work. When Criteria range is modified, isn't the filter automatic? The reapply icon is grayed out in my workbook. I had it work once, changing from Jul to Aug and the text AUG disappears after the update. I'm trying to make the table useful to a group easily look up a date with a simple choice.

SergeiBaklan

Diamond Contributor

Jul 20, 2021

Vince_Lunetta_1962

One way is VBA, on each change of any cell in criteria range it updates the filter. I believe HansVogelaar could suggest you perfect variant if concrete sample exists.

Another way to refresh is to click on Advanced Filter button when Ok, it remembers your previous setting.

And if you are on Excel 365 you may use FILTER() function:

Vince_Lunetta_1962
Copper Contributor
Jul 21, 2021
SergeiBaklan
Thanks for your responsiveness. I laughed when you mentioned VARIANT in light of the COVID stuff.
Is $G$2 a variable that limits the lookup? If I type a month in that location, the filter 'runs' and returns all those in July. Is that correct?

Bishops Birthday .xlsx186 KB
- SergeiBaklan
  Diamond Contributor
  Jul 21, 2021
  Vince_Lunetta_1962
  
  In G2 is just month number for criteria formula, you may hardcode month number directly into formula.
  
  If to copy into another sheet the workaround is here How to copy data to another worksheet with Advanced Filter in Excel? (extendoffice.com)