Summary sheet that only publishes rows with entries within a date range

[EnergyFX]

Hello,

So I am right at the tail end of a workbook I have been working on for a couple days. It's a rolling look ahead task tracking schedule with some auto hide features. The main sheet does everything I need it to (in part thanks to protonLeah). The last feature I would like to have is a slick summary page that only shows rows that have activities scheduled within the next 28 days.

Once I deploy this workbook the task list may get populated up to hundreds of items, but only a dozen or two will see progress over a 28 day period. Being able to filter out the tasks with no scheduled progress in the next 28 days would really help the crew leaders and foremen communicate and report their short term planning.

I have attached the full workbook. The "Summary" tab illustrates what I am trying to do automatically. (Note: On the "Calendar" sheet there is a VBA script in the background to handle the 4-week collapse (hide) feature and a couple macros to do/undo that feature. Nothing too fancy or concerning if you worry about activating macros when you open it.)

[AliGW]

Administrative Note:

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[Beamernsw]

Hi EnergyFX, I believe I have a solution to your query. I've added 2 helper columns in A and B and also a helper row at row 12, all of which are hidden.
The helper columns include a small lookup table to grab your weekday descriptions, this makes sure the day description follows the date each day.
And also a list of generated rows for tasks that include data during the 4 week period.

The helper row at row 12 holders the column numbers within the array to grab for your summary sheet.

Hopefully this is what you were looking for.

[EnergyFX]

Hi EnergyFX, I believe I have a solution to your query. I've added 2 helper columns in A and B and also a helper row at row 12, all of which are hidden.
The helper columns include a small lookup table to grab your weekday descriptions, this makes sure the day description follows the date each day.
And also a list of generated rows for tasks that include data during the 4 week period.

The helper row at row 12 holders the column numbers within the array to grab for your summary sheet.

Hopefully this is what you were looking for.

Beamer this is excellent. I've only spent a few minutes running it through its paces. I notice the query stops at Calendar! row 42 (which is the last row I had a sample task in place). I went through the Summary sheet and expanded your formulas out to row 300 for the Calendar! references and to row 100 for the Summary references. It seems to be working correctly when I add more sample tasks. I've also added another helper column at AR to conditionally format out the blank rows.

Gonna hold off on flagging this as SOLVED until I can stress test it a bit more and see if any bugs appear that I cannot solve on my own.

[Fluff13]

Another option without helpers, if you are on 365 or 2021
In C13 only

Please Login or Register  to view this content.

and in E13 dragged down only

Please Login or Register  to view this content.

[EnergyFX]

Another option without helpers, if you are on 365 or 2021
In C13 only

Please Login or Register  to view this content.

and in E13 dragged down only

Please Login or Register  to view this content.

Using the "Last Shift Date" column to sniff out what rows to publish is actually really clever and scales exceptionally well. Nice solution!!! I have Beamer's method working now, but I think I'm going to dedicate some time to exploring yours. I'll have to implement some conditional formatting to clean up the presentation, but that's pretty straight forward.

[Fluff13]

Glad to help & thanks for the feedback.