List Filter

Isaac

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:

=EOMONTH($Q$3,2)

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.