Forum Discussion
How do I filter data from one sheet to another based on dates?
If you have the newest version of Excel, there is a new function, FILTER, which does in formulaic form what the old menu selection did, and you can do it on separate sheets from the dataset, as you're wanting to do.
On your sheet3 in the attached, I've created an example. Here's the formula that appears in cell A5 of that sheet. Note, that's the only place where that formula appears. It "spills" to all the adjacent cells needed, in both rows and columns.
=FILTER(Table1,Table1[Review date]<D2) where D2 contains the formula =TODAY(), so as to get the list of overdue cases.
This is one of several new "Dynamic Array" functions. Here's a video that serves as a great intro to the set of Dynamic Array functions. https://www.youtube.com/watch?v=9I9DtFOVPIg
Thank you for replying.
When I open your attached file the formula doesn't seem to have worked - all the cells are showing "#NAME?". I do have the latest version of excel so that shouldn't be an issue. Did that formula work for you?
So frustrating, it just seems like it's impossible to do!
What do you mean under the latest version of Excel? Is that for Android, Mac or Windows? If Windows is that Excel 2019 or you are on subscription model, i.e. Office 365? Formula will work for the latest case.
SergeiBaklan It's for Windows. When I click on the "Account" button and then "About Excel", it says it's "Microsoft Excel 2019". However, just below the "About Excel" button it says "Version 2009", so that's a little confusing as to which I'm actually running.
As variant, for such sample
enter into D15 formula
=IFERROR( INDEX(Table1[Review date], AGGREGATE(15,6,1/(Table1[[Review date]:[Review date]]<$D$2)* (ROW(Table1[Review date])-ROW(Table1[[#Headers],[Review date]])), ROW()-ROW($D$14)) ), "")drag it to the left and to the right. Apply proper formats to A15:F15 (formula will show dates for numbers and blank cells), after drag entire line A15:F15 down till empty space will appear. Or as needed to have some gap for future data in Table1.
Yes, it does work for me.
I suspect you may not have the most recent. Try entering =FILTER and see what happens. If you have a version that supports that, it will (as it does for other functions) start prompting you to complete the formula.
mathetes The only option it gives me if =FILTERXML, not =FILTER. Does that make a difference?
These are functions with different functionality. FILTERXML parses the XML expression. FILTER filters data range based on defined condition and returns result to another range in the sheet.
