How do I filter data from one sheet to another based on dates?

[andyyporter]

I have a set of data which shows what dates risk assessments are due. I want to have 4 sheets; Sheet 1 for the master data, Sheet 2 to show overdue risk assessments, Sheet 3 to show which risk assessments are due within 1 month, and Sheet 4 to show which risk assessments are not due. 

I know I could just have 1 sheet and use the filter function to change what data is shown, but I'd rather have multiple sheets which all feed from the master sheet, so no manual filtering would be needed.

I've tried using the Power Query function but I'm a total novice and I got nowhere with it.

Any help would be wildly appreciated - even if it's only help setting up one of the 3 filter sheets.

Can someone help me please? I've attached a sample workbook to help.

[#2]

@andyyporter 

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

[#3]

@mathetes 

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!

[#4]

@andyyporter 

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.

[#5]

@andyyporter 

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.

[#6]

@mathetes The only option it gives me if =FILTERXML, not =FILTER. Does that make a difference?

[#7]

@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.

[#8]

@andyyporter 

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.

[#9]

@andyyporter 

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]

@andyyporter 

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.