Any method to poll many sheets, without using a collector sheet which requires constant updating?
Each of my projects has a Task List. And I need to send open tasks to my users who have open tasks. I accomplish this with a collector sheet that uses COUNTIFS to look at each Task List and identify who has open tasks. That sheet then has workflow to send an email to each person with at least one open tasks. That email has a link to a report, which uses Current User filter to show the user's open tasks.
Two problems with this:
- Each time I create a new project, I need to add the project's COUNTIFS formulas to the collector sheet.
- Those COUNTIFS forumulas need to reference each user who might have an open task, so every time someone new comes onto a project, we need to make sure that they are added to the COUNTIFS.
The alternative to this is to have one big task list for all of our projects. But that has its own issues, like we need to create a bunch of filters and providing project-specific drop-down options becomes impossible. By this I mean that a Task List has a Category drop-down column, and those categories should be project specific, which I can do with one sheet per project, but when all projects are in one sheet, that drop down gets really busy really fast.
The only solution I have come up with, and I have not tested it yet, is to create a workflow in each project Task Sheet that pushes (copies) its open tasks to a holding sheet each day at a certain time. That holding sheet is used as the filter to identify who should get the "you have open tasks" email. But that collector sheet will need to be emptied or contain some control to not alert people of today's open tasks based on yesterday's (or last week's) data.
Is there any other way to collect information from all of those sheets in order to trigger the workflow rule?
(I really wish we could trigger workflow rules from Reports, that would solve all of my problems!)