Use SmartSheet for Resource Planning

BlackDevil

Hey There,

I am new to smart sheet I want to use SmartSheet to...

• ... get an overview over all projects that are going on in the team.
• ... get an overview about the project status.
• ... see how many resources are available (aka how many manhours are available).

So far we using simple Excel sheets and oviously I want to get away from this. And I am looking for assistance So far I am using the trial version.

 

What I need is something like this:

1. A table that is showing the resources: I think of a calendar showing who is on vacation or has taken a day off, who is on a business trip, who is sick and who is available. It might also be that a person doesn't stay a full day.
2. A table that is showing all projects that are going on: this is a simple task I could solve by myself.
3. A table that is showing the projects, the calculated hours from PMO, the used hours, the still required hours and the resulting delta. To make it not that easy (I could solve the basic issue behind this) I want to have the still required hours and the used hours added on a weekly basis by still having the current delta.
4. A calendar that is showing how many manhours are available.
5. A matrix that is showing which team member is allocated to which project.
6. A "forecast table" to be able to add possible future projects and to have a forecast.

From 1. I get the available manhours per day that I can view in calender 4.. Moreover the sum of all current projects compared to the available manhours give me the current workload. From 5. I should be able to get the workload per team member.

Last but not least it should be possible to generate a biweekly report and have a dashboard (I think this is the easiest task).

 

What I am struggling with the most is how to get the dynamics into the tables and how to link the tables. Especially 3. is a hard nut to crack for me.

 

I hope my German english is understandable and that someone can assist

 

Thanks in advance!

BlackDevil

I put some more thinking in this topic and ended up with the attached tables.

 

What I am struggling with:

1. How can I calculate start and end date using the CWs?
2. How can I avoid to allocate an employee to projects even though he's not available? Note: availability may change from one week to another if the employee gets sick.
3. How can I manage to have dynamic dropdown menus to select multiple projects per employee (table 3)?
4. How can I fill table 4 dynamically with the projects from table 2?

 

Right now I try to help me with the template set PTO&Project Status to learn about the table connection. But I still need some assistance

 

Thanks in advance!

BlackDevil

No one?

BlackDevil

Now, I did manage to have a table "Project List", and a table "Team Workload". I've managed to use SUMIFS to calculate the total hours per calender week in Team Workload from Project List. However, I had to add a column to the project list providing the result of the total hours allocated to a certain project divided by the duration. In Excel I could use SUMPRODUCT, but that doesn't exist in SmartSheet.

What I am struggling with right now:

1. In a table "Current project hours" I want to show all the projects that are not marked "End" within the table Project List. I managed to use a combination of JOIN and COLLECT to do so, but then I have everything in one cell. I couldn't find a solution to separate the data again.
2. Our projects are organized in calender weeks. However, I want to have the chance to use the Gantt feature to show the project list also as a Gantt chart. Therefore I need the start and end date as a date not as calender week. While I could manage to do so in Excel I couldn't in SmartSheet. =DATE(2019;1;7*[@[Start (KW)]]-3-WEEKDAY(DATE(2019;;);3))

There will be more issues along the way but this is it for now. Maybe someone can help me

 

Thanks in advance!

BlackDevil

Workaround for 1): In the table Current Project Hours I used the first column to link to the "End" column in the project list. I filter this column so that it shows only those values that are not true. Result is that I only see project numbers that have not yet been endet.

Sean Morgan

Hi there BlackDevil,

 

I'd love to help with the issue you are encountering.  May I ask what you are attempting to accomplish with your formula? From what I can see, you are using the WEEKDAY function. This will return a day of the week via a number format. Are you able to provide screenshots of your sheets so we can help provide a solution and better determine the issue.

 

Regards

 

Sean