Scheduling multiple projects with multiple resources - Gantt

MicheleZ

I have a created a sheet that has a column for each of the following:

Project Name

Project Team (Contact List with multiple names)

Start Date

End Date

I want to be able to create a Gantt view that shows each individual resource same and then shows the projects they are assigned to in a timeline view by week. Right now we do this in excel and it is REALLY manual especially if the dates of the project shift, you have to update each row for each person copy and paste formatting. see below. Anyone have any tips on the best way to set this up. Ideally I would like to be able to make adjustments to the start and end date and have gantt auto refresh to show a similar view.

Note: I am sure this can easily be done in resource management, but unfortunately that is not an option right now (I assume this is an added cost)

Thanks in advance.

Genevieve P.

Hi @MicheleZ

This will be better if you could have the Contact column be single-select instead of multiple. OR if you had a "Team" column with a single team listed, versus multiple people.

Then you could use a Report to Group by the Contact column and visualize your contacts just like how you have above.

If this isn't helpful, can we see a screen capture of how you have your data in the source sheet? But please block out sensitive data.

Cheers,

Genevieve

MicheleZ

There isn't a source sheet right now. We just manually input everything in excel and format so it looks like the visual above. Right now I am having a hard time formatting my data in smartsheet to get some kind of similar Gantt output. I am not able to group by team as we need visibility individual staff project schedules. The data I have is a list of our team employees (name), a list of planned audit projects for the year. Then I will populate the start and end dates for each project.

These dates sometimes change, so I am looking for an easy solution that will show a Gantt chart for each team member that details all the projects they are assigned too. Then I am hoping to have it be dynamic so if I change the date it will update the gantt chart. If I need to reassign staff I can. Right now the only solution I have found is to create a something like below.

It just seems time consuming to have to have multiple rows per audit project for each resource assigned. and if the date changes, I would have to update that multiple times. I am open to any suggestions on a better approach

Genevieve P.

Hi @MicheleZ

Using that example data, this is the Gantt chart I created by Grouping the Report by the Team Resource column, and using Conditional Formatting on the source sheet to change the Task Bar colours based on the Project name.

You can adjust the dates right in the Report to change the Gantt bars 🙂

Let me know if this would work for you!

Cheers,

Genevieve

MicheleZ

Yes! This would work! So did you set up the the smart sheet data in the same format as the example I provided? And then create a report off of that? Did you have to format each project color on the report manually? I am relatively new to SS, so sorry if these are super basic questions!

Genevieve P.

Hi @MicheleZ

Glad to hear this will work!

Yes, here's the sheet.

• The Audit Project is the Primary Column
• The Team Resource is a single dropdown list (but it could be a Contact list if you wanted)
• Then the 2 date columns are set as Dates

Notice that I have Conditional Formatting to change the Task Bar based on the Audit Project. This would then change the colour automatically! Here's more information: Apply automatic formatting with conditional formatting rules

Then in the Report I using the Grouping function. You can also Sort by Start Date for the order of your tasks. Here's a free webinar on Report Grouping & Summary: Redesigned Reports with Grouping and Summary Functions

Cheers,

Genevieve

MicheleZ

Amazing. Thank you so much!!