Forum Discussion

andyyporter's avatar
andyyporter
Copper Contributor
Oct 22, 2020

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

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.
excel
Formulas and Functions

9 Replies

  • mathetes's avatar
    mathetes
    Silver Contributor
    Oct 22, 2020

     

    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

     

    • andyyporter's avatar
      andyyporter
      Copper Contributor
      Oct 22, 2020

      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!

       

       

       

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Oct 22, 2020

        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.

Resources