Team resource planner

Harvey E

Hi

I would like to use Smartsheet to plan team resources against projects. Each member of my team should have access to a sheet specific to them where they can record the projects they're working on and the forecast number of days per week.

Then I would like to be able to report on each of these individual sheets in one place so that I can see who is under or over-allocated each week.

I had built sheets for the team with week commencing dates in one column, and then each project added as a new column with the estimates per week within it.

My challenge is how to collate the stats, without it being a manual set up each time someone adds a project. I had hoped to use a report and adding all sheets to the report, however reports will show each sheet one below the other, ie even if the same date exists in 3 separate sheets, they will be reported separately so I can't see side-by-side who is busy/when.

Any ideas? I don't have access to Smartsheets resource add-on but not sure it's the right solution anyway.

Thanks!

Andrée Starå

Hi @Harvey E

I hope you're well and safe!

Have you tried using the Grouping/Sum feature in the Report?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Harvey E

Thanks for the suggestion, but unfortunately it's not the answer. Whilst I can group on date, it will still show individual line entries for each person. In the example below I have also put the date into the primary column and tried to summarise on that. Ideally what I need is a single line per date...

Genevieve P.

Hi @Harvey E

I agree that a Report would be the easiest way to get this information as new sheets are created.

What I would suggest here is ensure that the Column Name is the same across all your Reports (e.g. "Total Allocation"). Then include in the Sheet Name column and have the employee name associated with the Sheet Name.

That way all of your values are listed down one column next to the emloyee's name.

If you want to see this horizontally, then I would go with @Andrée Starå's solution of using the Summary function along with your Grouped report (in the image above). Then you can collapse all the Grouped rows, so the Date shows in the Primary column and the SUM value shows in each respective "Total Allocation" column next to it.

You can set the Report so it always shows grouped rows as collapsed, only showing one line per date.

Cheers,

Genevieve

Harvey E

Hi

Thanks for your responses. I've resorted to doing this the other way around - I have one (potentially massive) sheet containing all of the data for all team members. I've then created a separate report for each member of the team to update their own data.

Now I'm looking at creating reports and dashboards on utilisation etc.

I want to create a report that shows availability by skill type, so at the top of each column I have the name on Row 1 and the skill on Row 2, then each Row beneath that shows their availability for a particular date (Date is another column). To total up all the resources of a particular type on a particular date, I can use this formula (here it is looking for PM skill type).

=SUMIF(END$2:Utilisation$2, "PM", END@row:Utilisation@row)

The formula works, but I can't convert it to a column formula, which means every time I add new date rows, also have to copy down the formula. I'm trying to make this as easy to use as possible, is there any way to come up with a column formula which will do the same thing?

Also is there any way to reference a column name in a formula? For example if I set a column to be called "Joe Bloggs", can I access that name and use it to search in the sheet for any reference to it within the data?

Thank you!

Genevieve P.

Hi @Harvey E

Even though your formula isn't locked as a column formula, when you add new rows to the sheet it will auto-populate based on specific conditions. For example, if you add the new row immediately beneath 2 rows with the same formula, or between two rows. See: Use or Override Automatic Formatting and Formula Autofill

In regards to column names, no, there isn't a way to search for a column using it's name within a formula. Depending on what you're looking to do, you could use the INDEX function. If your first range is multiple columns, you can then use the column number to reference it (this means it won't break if the column name changes).

Cheers,

Genevieve