Counting overdue tasks from a lot of project sheets
Each project sheet has basically an identical structure and each line item is assigned to a specific person/user (Contact list). There are approximately 300 lines per project sheet.
- Each user has a report that shows only the tasks assigned to them from all the source sheets.
- Previously, we had a dashboard that showed each user and the number of overdue tasks on a bar chart.
- We now are struggling to display the overdue tasks on a graph because there is 30-40 source sheets.
Can anyone please help with a solution?
Best Answer
-
Hi @TeeM
This depends on how your project sheets are set up, but the way I would do it would be to create a section in each of the project sheets that's calculating the totals for me, within that sheet - per person/status.
Then in a separate sheet specifically for the graph, you can cell-link in that calculation from each sheet per person, using a simple SUM formula to create the total. In my example I made all the Project sheets child rows, with the Total being the Parent row, but you can organize your sheet how you want:
This would be better than doing cross-sheet formulas because you can only have 100 distinct cross-sheet references in one sheet. If you were trying to do the COUNTIF within the chart data sheet you'd run into this limit.
Let me know what you think!
-Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
Hi Terry,
How many are the users?
I hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. 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.
-
Hello Andree,
There will be 5 x primary users with a number of additional sub-contractors to be added in due course.
Kind regards,
Terry
-
Hello All,
Is anybody able to help here?
-
What do you mean by "display on a graph"? Are you using some type of chart widget, or do you mean in the report?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi @TeeM
This depends on how your project sheets are set up, but the way I would do it would be to create a section in each of the project sheets that's calculating the totals for me, within that sheet - per person/status.
Then in a separate sheet specifically for the graph, you can cell-link in that calculation from each sheet per person, using a simple SUM formula to create the total. In my example I made all the Project sheets child rows, with the Total being the Parent row, but you can organize your sheet how you want:
This would be better than doing cross-sheet formulas because you can only have 100 distinct cross-sheet references in one sheet. If you were trying to do the COUNTIF within the chart data sheet you'd run into this limit.
Let me know what you think!
-Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Paul Newcome I have a report sheet that collates the data from other sheets. This is then displayed as a graph on a Dashboard. Trust that answers.
@Genevieve P - Can you please help with the formula that will automatically change the color of the balls according to whether the date is today (Yellow), or overdue (Red)? In the template provided by Smartsheet, this is a manual operation. I have tried a number of variations unsuccessfully. Many thanks.
-
@Genevieve P - Your solution above has been great. Thank you. Much appreciated.
-
@TeeM I see you were able to get your original question answered.
Did you still need assistance with automating the RYG? There are A LOT of posts here in the Community referencing many different options and variations for automating these. If you are unable to find one that works for you and still need help with it, let us know.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hello Paul, this was covered in a webinar this morning. Thank you.
-
Great! Glad you were able to sort it out. Let us know if you have any other questions. 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Excellent! 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I saw that Paul and Genevieve answered already!
Let me know if I can help with anything else!
Best,
Andrée
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.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 377 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives