Counting overdue tasks from a lot of project sheets

TeeM

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?

Genevieve P.

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

Andrée Starå

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!

TeeM

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

TeeM

Hello All,

Is anybody able to help here?

Paul Newcome

What do you mean by "display on a graph"? Are you using some type of chart widget, or do you mean in the report?

Genevieve P.

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

TeeM

@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.

TeeM

@Genevieve P - Your solution above has been great. Thank you. Much appreciated.

Paul Newcome

@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.

TeeM

Hello Paul, this was covered in a webinar this morning. Thank you.

Genevieve P.

Great! Glad you were able to sort it out. Let us know if you have any other questions. 🙂

Paul Newcome

Excellent! 👍️

Andrée Starå

@TeeM

I saw that Paul and Genevieve answered already!

Let me know if I can help with anything else!

Best, 

Andrée