Report for Overdue Tasks for multiple Team members

TeeM

KPH

Ahh! Your assigned to column is a multi-select isn't it? Smartsheet does not allow Group By on multi-select columns.

TeeM

any alternative?

KPH

You could set up some helper columns with formula to split the people out, but that it is probably as much effort as making a metric sheet with COUNTIFS. I would look at the COUNTIF functions. As you have a multiselect dropdown you will need to use the HAS function when you get to the team member part:

=COUNTIFS({Team member % Complete},<1, {team member End Date},<=TODAY(2),{team member End Date},>=TODAY(),{team member Name},HAS(@cell, "Burt"))

TeeM

OK ....so if i have this correct, I would have this formula on the METRICS sheet. How can I pickup/cross-reference from all the source sheets as a group like I can with a report?

KPH

You need to set up a cross sheet reference to each of your source sheets. As you type the formula a box will pop up with a link in it to reference another sheet. Click that, select your sheet, select the column, give it a name (a good conversion is to use something that identifies the sheet and something that identifies the column, ie Team A complete col), then clock OK. The name will be added to the formula, surrounded by curly brackets.

TeeM

At the moment there are 14 source sheets and these are continually increasing in number month by month. Is there a way to pick up all these sheets rather than cross referencing every sheet individually?

Can I cross reference to a workspace?

KPH

Each cross reference needs to be set up individually, I'm afraid.

TeeM

😒