List Filter


Hi Team,

Following on from the Margic Filter excel video, please find attached a list my date that I would like help with.

I've added a date column and on the list sheet with criteria for each Qtr.

I want to filter by each Qtr 0, and I should have the same visibility by header.

Note on columns B-M these data will be coming from vlookups from another sheet so apply filter on the sheet won't be viable. as this is the main sheet.




Selected Answer


I can't see "Creation Date" in your Data Input  sheet so I've used Qtr 0 to Qtr 3 plus Prv FY (previous finacial year) as the filter items and used the approach from Don's video tutorial Excel Date Filter Magic - Last Month, This Month, YTD & More + Awesome Interface.

To do that, you'll see that I've converted your rows (in Data Input) into a Table called QtrData in the sheet Raw Data.

That's used in the filter array formula in cell B7 in the sheet Filtered by formula, as follows:

=FILTER(QtrData[#Data],QtrData[Force Period]=E3,"")

Above that you'll see cell E3 has a (List) data validation drop-down (from cells $P$5:$P$9) allowing you to pick the financial period (and the filtered list below will respond to suit).

(Note I added a bogus line for Q3- please delete from Raw Data once you've checked it!)

Note that the totals are above the filtered data (in I3, L3 and M3) and use the Subtotals formula to calculate the money values.

If your data will include calendar dates then you might use the table QtrDates in P3:R9. You only have to enter the Financial Year start date into Q3 (the cell shaded yellow) and it uses Excel's EOMONTH function to calculate the Qtr starts/ end dates automatically. E.g. to calculate the end of Qtr1, it uses:

where the 2 adds two months to the date in Q3 (so June) and EOMONTH gives the date of the end of that month (i.e. gives a date 3 calendar months of FY start). The PRV FY row uses other date calculations.

When E3 changes then cells Q12 and R12 give the start date end dates to filter the Creation Dates.

Hope this helps.



Thanks John for your support.
siehartley2001 (rep: 8) Jul 29, '21 at 8:30 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login