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

  • Genevieve P.
    Genevieve P. Employee Admin
    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!