Creating daily work schedule for individual team members (from a master project grid).

Jon_jls9fb

Hello,

I'm building a system in Smartsheet to manage work requests across all projects in my organization. The basic idea is for Project Managers to submit task orders to a common grid, which are then assigned to various team members. Ultimately, this new system will serve as the primary work scheduling tool.

The system is up and running, but I've received some feedback from our crew leaders that it's difficult for the assigned team members (who aren't Smartsheet users) to understand where they are to be working on any given day. These team members are used to receiving a report that shows the assignment of each individual team member for the day. Below is a screenshot of what the team members are accustomed to receiving (names blacked out). To be clear, this report is NOT from the system I'm building (it's from the system I'm trying to replace):

The system I'm building is fundamentally different from the above. All Task Orders will be submitted with a defined Start/End date. Team members will be assigned to a 'block' of work (rather than creating person/day records as shown above). I expect the new system will be more efficient, but I'd like to make the transition easy and provide an output that looks more familiar to the team.

My question - From a 'master' grid like I've described (task orders with start/end dates and assigned team members), is it possible to create a report similar to the above? A couple of other notes:

*In many cases, multiple team members will be assigned to the same project on a single day and should appear in the same record together.

*For several reasons. 'Resource View' is not an option.

Darla Brown

Hi,

Do you have access to Dynamic View? If so, create a Current View filter based on the Team Member column. This way, they can see their tasks and provide updates, comments, attachments, etc from that view.

Is this an option?

Jon_jls9fb

Thanks Darla. We don't have Dynamic View, and if I understand correctly, it wouldn't be a complete solution anyway. Dynamic View seems useful for displaying a 'filtered' view of work blocks, but it wouldn't help me convert the blocks into a daily schedule (like the one shown in my post).

jmyzk_cloudsmart_jp

Hi @Jon_jls9fb

Here is a solution that uses a combination of helper columns and Smartsheet formulas to map team members, projects, and locations from the master grid onto a project calendar sheet. Here's a detailed explanation of each formula:

Demo Master Grid Sheet

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

Project Calendar Sheet

The Project Calendar Sheet is structured to display a weekly view of assignments for projects and team members, using a combination of parent rows for each day of the week and child rows for specific project details. Here's how the sheet is organized and how each component functions:

Structure of the Project Calendar Sheet
Parent Rows (14 rows for 2 weeks): Each parent row represents a specific day of the week.

For example:

Row 1: Monday (Week 1)
Row 2: Tuesday (Week 1)
...
Row 7: Sunday (Week 1)
Row 8: Monday (Week 2)
...
Row 14: Sunday (Week 2)

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

Purpose:

Acts as a container for the child rows * that hold detailed project information for that day.

• Each parent row has five child rows dedicated to listing projects and their details for that specific day.
• Columns in child rows:
  • Project Name: Displays the project(s) assigned for that day.
  • Team Members: Lists the team members working on the project.
  • Location: Shows the location where the project work is being carried out.

Formulas

[isParent] =IF(COUNT(CHILDREN()) > 0, 1)
[HasProject] =IF(COUNT(CHILDREN(Project@row)) > 0, 1)
[ParentNum] =MATCH([Row ID]@row, COLLECT([Row ID]:[Row ID], isParent:isParent, 1)) - 1
[Date] =IF([Row ID]@row = 1, TODAY(-WEEKDAY(TODAY())), IF(isParent@row, TODAY((-WEEKDAY(TODAY())) + ParentNum@row)))
[Team Members] =IF(NOT(isParent@row), IFERROR(INDEX(COLLECT({Master Grid Sample : Team Members}, {Master Grid Sample : Start Date}, PARENT(Date@row) >= @cell, {Master Grid Sample : End Date}, PARENT(Date@row) <= @cell), [Days of the Week]@row), ""))
[Project] =IF(NOT(isParent@row), IFERROR(INDEX(COLLECT({Master Grid Sample : Project}, {Master Grid Sample : Start Date}, PARENT(Date@row) >= @cell, {Master Grid Sample : End Date}, PARENT(Date@row) <= @cell), [Days of the Week]@row), ""))
[Location] =IF(NOT(isParent@row), IFERROR(INDEX(COLLECT({Master Grid Sample : Location}, {Master Grid Sample : Start Date}, PARENT(Date@row) >= @cell, {Master Grid Sample : End Date}, PARENT(Date@row) <= @cell), [Days of the Week]@row), ""))

Explanation of some formulas

[ParentNum]
=MATCH([Row ID]@row, COLLECT([Row ID]:[Row ID], isParent:isParent, 1)) - 1
Assigns a sequential "parent number" to each parent row based on its position in the list of parent rows.

[Date]
=IF([Row ID]@row = 1, TODAY(-WEEKDAY(TODAY())), IF(isParent@row, TODAY((-WEEKDAY(TODAY())) + ParentNum@row)))
Automatically generates dates for parent rows, starting from the first day of the current week and incrementing for each subsequent parent row.

[Team Members]
=IF(NOT(isParent@row), IFERROR(INDEX(COLLECT({Master Grid Sample : Team Members}, {Master Grid Sample : Start Date}, PARENT(Date@row) >= @cell, {Master Grid Sample : End Date}, PARENT(Date@row) <= @cell), [Days of the Week]@row), ""))
Retrieves the team members assigned to a project on a specific day.
Logic:

The COLLECT function filters team members from the master grid based on The Start Date and End Date columns in the master grid matching the current date (in the parent row).
The INDEX function retrieves the result corresponding to the current day of the week.

The [Project] and [Location] columns have the same formula structure, changing the collection range.

Report that shows dates with project

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