Creating a heat map to understand level of effort across a team

rcfjcc

Hi! We would like to understand which projects are requiring more effort, by week and by person (i.e. where is the team spending most of their time this week). The challenge is that the sheet is designed for comms plans vs. project or resource management, so duration isn't an accurate indication of time spent. I included an excerpt of one of the smartsheets in case it's helpful to see.

The team inputs a specific forum or tool (for example, a newsletter) as the task, and that row also includes their name (submitter), start date and initiative. Each person on the team has their own sheet. It would be difficult to change the layout of the sheet since it feeds into sheets used by other teams, and we don't want to ask the team to input more data, but I could add helper rows.

My initial thought was to build a report that looks across the sheets and counts the number of tasks within an initiative that are in a specific timeframe (for example, count the number of tasks from May 1 - May 15), and if the tasks are above X number then the initiative shows up on a dashboard with the person's name. Would it be easier to have a helper column in each sheet? Is there a better/ easier way to do this? I've already looked at the SS templates and don't see anything that feels right. Thanks all!

LearningDeployment_Julie.xlsx

jmyzk_cloudsmart_jp

Hi @rcfjcc

Thank you for sharing the Excel file.

Here is a demo solution.

https://app.smartsheet.com/b/publish?EQBCT=c169314144c34303816f92691e1bbca2

For the solution, I created a metric sheet that counts the number os tasks by submitter during a specific period.

As the image shows, I used the Sheet Summary to designate the period.

https://app.smartsheet.com/b/publish?EQBCT=88fdc04f6c964f8d8ee5c3157cb63e91 (The metric Sheet at the bottom of the dashboard)

As I wanted to populate the submitters dynamically, I added column numbers from 1 to 10 in the # column.

Then, using the INDEX(DISTINCT()) function, I listed the submitters' emails.

[Submittter] =IFERROR(INDEX(DISTINCT({LearningDeployment_Julie : Submitter}), [#]@row), "")

[Number of tasks] =IF(ISBLANK(Submittter@row), "", COUNTIFS({LearningDeployment_Julie : Name}, ISTEXT(@cell), {LearningDeployment_Julie : Submitter}, Submittter@row, {LearningDeployment_Julie : not Parent}, true, {LearningDeployment_Julie : Date}, <=End#, {LearningDeployment_Julie : End Date}, >=Start#))

[Rank] =IF(ISNUMBER([Number of tasks]@row), RANKEQ([Number of tasks]@row, [Number of tasks]:[Number of tasks], 0))
[Top 3] =IF(ISNUMBER(Rank@row), RANKEQ(Rank@row, Rank:Rank, 1) <= 3)

To prevent counting the parent's task, I added the [not Parent] column, as shown below. If you want to include the parent task as well, remove the bold part.

COUNTIFS({LearningDeployment_Julie : Name}, ISTEXT(@cell), {LearningDeployment_Julie : Submitter}, Submittter@row, {LearningDeployment_Julie : not Parent}, true, {LearningDeployment_Julie : Date}, <=End#, {LearningDeployment_Julie : End Date}, >=Start#))

The conditions that compare the Date and End Date with the Summary Fields get the number of tasks in the period.

https://app.smartsheet.com/b/publish?EQBCT=9a1ff0b50af3468d93c4e70ab700d776 (You can access the published sheet and apply the filter to check if the formula is working correctly.)

By the way, the APAC Smartsheet Aligned Bootcamp has been held at the Marriott Hotel since the year before last. I stayed at the first one in Bangkok, and the bed was the best I have ever had.😊