10-22-2020 05:24 AM
10-22-2020 05:24 AM
10-22-2020 05:56 AM - edited 10-22-2020 05:58 AM
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
10-22-2020 07:29 AM
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!
10-22-2020 07:40 AM
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.
10-22-2020 01:49 PM
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.
10-23-2020 07:45 AM
@mathetes The only option it gives me if =FILTERXML, not =FILTER. Does that make a difference?
10-23-2020 07:48 AM
@Sergei Baklan 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.
10-23-2020 08:21 AM
Excel 2019 is the name of the product. Version 2009 is the build version of the product, after it in parentheses is concrete build number.
10-23-2020 08:23 AM
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.
10-23-2020 08:56 AM
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.