Rolling Filter for Table

krisso

Active Member
Joined
Sep 16, 2005
Messages
291
I have a table that is filtered by monday dates to pick up the current monday date and the previous 10 mondays to create a rolling summary. I am wondering is there any way I can setup a dynamic filter so it always picks up the previous 10 weeks each monday rather than manually removing one date from the filter and adding a date to the filter each monday?

Thanks
Chris
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Do you actually want to filter OUT other week days Tue-Sun, and ONLY show data for Mondays ?

I'm guessing NO, I'm guessing you really only want data for the last 10 weeks, regardless of what weekday it is.
In which case in your filter you can use something like
Is after or equal to
TODAY()-70

That might sometimes lead to you picking up the wrong Monday, if that's a problem, you can adapt to something like
TODAY()-WEEKDAY(TODAY(),3)-70
Play around with the 3 value in the WEEKDAY function.
 

krisso

Active Member
Joined
Sep 16, 2005
Messages
291
Sorry Gerald,

I didn't make that clear in my post. My table only shows Monday dates. It bascially picks up a file count for the week from the start of each monday. Currently each monday I manually filter out the date that is 12 weeks old and pick up the new week date.

I have tried both of your suggestions and get a blank return

If it helps this is the formula used in the table =COUNTIFS(Matter[[#All],[Fee Earner]],'New Matter Opened'!B$2,Matter[[#All],[matOpenDate]],">="&'New Matter Opened'!$A67,Matter[[#All],[matOpenDate]],"<"&'New Matter Opened'!$A68)
 
Last edited:

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
OK so it was 10 weeks in the OP, now it's 12 weeks :)

I'm assuming you're using eitherExcel's Data, Filter, function OR an Excel table - either way the filter functionality seems to be basically the same.
And I'm assuming the column you are filtering on actually has an Excel data in it - is this correct ?

Can you confirm the above please, and if YES, we should be able to get a version of my formula to work, I'll post back on that.
 

krisso

Active Member
Joined
Sep 16, 2005
Messages
291

ADVERTISEMENT

I think my original post was correct, that it picks up the currrent weeks totals plus the previous 10 weeks ;) what I said is each monday I manually remove the 12th week when bringing in the new week ;)

The table dates look like this

11 wk rolling totals
Date
24/06/2019
01/07/2019
08/07/2019
15/07/2019
22/07/2019
29/07/2019
05/08/2019
12/08/2019
19/08/2019
26/08/2019
02/09/2019

I am indeed using the filter with in the table to pick up these dates and you are correct the column does have data in it

Thank you for your help :)
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Hmm . . . well this is embarassing . . . I was convinced that you'd be able to apply a formula to the filter criteria, to select a certain number of weeks back, but I can't actually seem to get that to work.

Another option would be to run a short macro which could easily do this - is that an option for you ?
 

krisso

Active Member
Joined
Sep 16, 2005
Messages
291

ADVERTISEMENT

I was convinced that a fomula in the filter would do the trick but failed spectacularlyin my efforts. I was hopeful that your formula was going to work as I had put = before my formula's

Was hoping to do it by formula rather than Macro as the person that uses the spreadsheet is a bit of a technophobe, and when I am not in the office I was hoping for something just to update automatically
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Yeah sorry about that - maybe there's a trick to getting Data Filter to work that way, but I don't know what it is.

If you decide to go down the code route, this might get you started - works for me on a basic level . . .

Code:
Dim Startdate As Single
Startdate = Date
Startdate = Startdate - 70

    Range("Table1[#Headers]").Select
    Selection.AutoFilter
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _
        " >= " & Startdate, Operator:=xlAnd

I'm not a code expert, there may be better ways of doing this.
The number 70 is obviously meant to calculate the date 10 weeks before today's date.
If this should be 12 weeks, use 84 and so on.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,312
Messages
5,600,887
Members
414,414
Latest member
neil_c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top