Count of Past Due Tasks by Assigned Over Multiple Sheets

TJ Norris

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?

James Keuning

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.

BullandKhmer

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.

TJ Norris

@BullandKhmer

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?

Andrée Starå

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!

TJ Norris

@Andrée Starå

I'm good using any method to create these metrics. Any advice on how to do this via a report?

Andrée Starå

@TJ Norris

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?

TJ Norris

@Andrée Starå

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.

BullandKhmer

@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

1. 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.
2. 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.
3. 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.

TJ Norris

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.

David Tutwiler

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

TJ Norris

@David Tutwiler Thanks for the tip. I had not come across this before. I'll look into it.

Andrée Starå

@David Tutwiler

You're probably thinking of Data Shuttle. I don't think it's possible with Datamesh.

Andrée Starå

@TJ Norris

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.

TJ Norris

Thanks @Andrée Starå. I'll take a look at that too.