Display Rows From Another Sheet Based On Range Of Dates

0
votes

I'll try to explain this as best as I can, I'm not super experienced with spreadsheet formulas so bear with me.

Currently I have 2 Google spreadsheets, one contains a list of all client related tasks. The second spreadsheet is client specific, it has a few sheets such as "Feeder", "Archived Requests", "Monthly Report". The "Archived Requests" sheet lists all the data assigned to a specific client from my first spreadsheet using QUERY IMPORTRANGE:

=Query(IMPORTRANGE("key","spreadsheet-name!A:Z"),"Select Col2, Col3, Col4, Col5 where Col1 contains 'TOSC'",1)

This part works all fine and good.

My "Feeder" sheet lists some =DATE and =EOMONTH formulas which I have made into Named Ranges.

What I'd like to do, ideally, is display all data from the "Archived Requests" sheet on the "Monthly Report" sheet between the named range "ReportMonthStart" and "PriorMonthStart".

Essentially, what I'm hoping to achieve is a dynamic listing of row data that goes back 30 days from the 15th of each month.

I'm not sure if this should/can be done with a Query or a Filter, or any other formula.

Here is the link to my current spreadsheet.

1
google-sheetsworksheet-functionformulas

1 Answers

1
votes

The filter does this nicely:

=filter('Archived Requests'!A2:E, ('Archived Requests'!A2:A >= PriorMonthStart) * ('Archived Requests'!A2:A <= ReportMonthStart))

This returns A-E entries of the rows where A column is between PriorMonthStart and ReportMonthStart. Multiplication of two conditions is logical AND.