Count of Past Due Tasks by Assigned Over Multiple Sheets
I have a few similar projects. I am running similar sheets for each of them. I want to create a separate sheet/report that shows me, across all projects, the total count of past due tasks by Assigned To person. I'm looking to create something like the attached image. The list of Assigned persons can change at any time on any of the tasks on the main project sheets. Some contacts may not be on all three projects. I would like to avoid having to manually maintain the "Assigned To" column on this new sheet/report. Is this possible?
Answers
-
I have this problem, and I have more than 50 task sheets.
I created a rule in my template, so this rule is in every task sheet. The rule copies the overdue tasks to one collector sheet every morning at 3AM. Then that collector sheet has an alert that it sends to me and all of the people who have tasks assigned to them. The collector sheet has a system field for date created, so the alert only sends alerts to people who have records that we pushed that day.
-
So, looks like columns B, C & D would use countifs formula, something like
=COUNTIFS([sheet x assigned to col], =[col A], [sheet x due date col], <today())
Make sense?
I cant see why Total past due wouldn't be b+c+d?
Hope that helps.
AJ.
-
Any idea how to populate column A to pull a unique list of Assigned To persons from all project sheets?
Any way to keep that list updated as new Assigned To people are added on Project Sheets?
-
Hi @TJ Norris
I hope you're well and safe!
To add to previous excellent advice/answers.
Have you explored using a report instead?
Would that work/help?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I'm good using any method to create these metrics. Any advice on how to do this via a report?
-
If I understand it correctly, you should be able to use the Grouping feature in a Report.
Group by assigned to first and then by project and then by due or something similar.
Make sense?
Would that work?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
In a report, there doesn't seem to be the ability to group by the "Assigned To" column or to summarize by "Assigned To". See screenshot. I'm pulling from my four project sheets. The filter is set to past due tasks only that are not in completed status.
If you reference the screenshot in my original post, I would like to get a distinct/unique list of Assigned To contacts and the total number of past due tasks they have on each sheet.
-
@TJ Norris Just looking at that last screenshot, is it possible that you have filtered assigned to or included a sheet without an assigned to coulumn... I'm not sure, but you can usually group by assigned to in reports.
if you wanted to revert back to the sheet method then your getting into pretty complicated territory. best I can think of is
- Set up an automation where when a row is added to any source sheet that row & specifically the assigned to column is copied to a new meta data sheet.
- Helper column in the meta data sheet which designates which rows are unique through countifs and assigns a sequential number to them. 1.2.3.4.
- Then on your destination sheet have a helper column with 1.2.3...... 1000000 and use a vlookup to find the names from the meta sheet.
Would take me hours to figure out the detail. If you're lucky @Paul Newcome might get this and write the formula, maybe.
Cheers,
AJ.
-
Thanks @BullandKhmer. Looking around the community over the last few days, it seems like there's not an easy way to pull a distinct list of contacts from multiple lists and keep it updated. I've run into this before and it would be a nice enhancement to Smartsheet.
-
Have you ever looked into Data Mesh? I'm fairly certain one of its calling cards is being able to update contact lists from a central point to multiple sheets throughout your build. One of its benefits over formulas is that it can update Contact Lists and maintain that integrity.
https://www.smartsheet.com/marketplace/premium-apps/datamesh
-
@David Tutwiler Thanks for the tip. I had not come across this before. I'll look into it.
-
You're probably thinking of Data Shuttle. I don't think it's possible with Datamesh.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I haven't forgotten about you. I'll get back to the post later.
In the meanwhile, I recommend having a look at Data Shuttle.
I've developed client solutions using it to keep dropdown/contact lists updated.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thanks @Andrée Starå. I'll take a look at that too.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives