Auto Reapply Filter without VBA or Macro?

Maybe this feature does not exist but I would like to know how to set my worksheet to auto-reapply filters.

I know how to do it using a simple macro with the following code.

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.AutoFilter.ApplyFilter

End Sub

However, I work in an office environment where macros are disabled by group policies. I need a workaround if one exists.

The worksheet shows a monthly calendar for each month. I have a dropdown that allows me to select a given month. I have a filter that allows me to hide every month but the selected Month. However, I have to reapply the filter every time I choose a different month from the dropdown. Is there a way to automate the filter without macros or VBAs? Thanks

Instead of having 12 sheets, you could just have one sheet where you select the month (and year) from a drop-down. You would need to have formulae to generate the dates (taking into account the month and year selected), but that is fairly straightforward.

You don't explain what you use this file for, but you seem to have 7 rows for each day, so this would allow you to display up to 7 different events. Instead of putting these events directly into the calendar, you could record them on a separate sheet, and then other formulae could retrieve the data from there and display it on the calendar sheet on the appropriate day.

Hope this helps.

Pete

Great minds think alike... If you click on the filter labeled hide and check the "hide" box you will see that I already have all the months on one sheet as well as formulas that generate the word "hide" based on the month and year selected. The data that is put in the cells of these filtered calendars are displayed on other sheets on the appropriate day. Essentially the same thing as your recommendation just reversed.

The issue is having to manually select reapply after selecting the desired month from the dropdown. I understand that formulas can be set to calculate automatically or manually, but how annoying would it be if every formula had to be reapplied? Regardless, the formulas are set to automatic as the word "hide" is displayed for any month selected (other than the currently displayed month). Why the filter feature doesn't automatically hide the cells with the words "hide" is perplexing.

I am not completely understanding what you are looking to do, but I have made similar books where I will have a dynamic date range (usually rolling 8 weeks or similar) and I want to auto filter in a Pivot table. What I do is add a helper column to the end of the data that looks something like:

Please Login or Register  to view this content.

And then you filter for TRUE or FALSE. As the dates change in the formulas that calculate dates (you might use TODAY() as your DataDate for instance) the formula will update to evaluate as TRUE/FALSE and your filter will follow along nicely.