How can I create a resource allocation summary to show my planned v/s actual resource allocation?

Niyati Kumar

My Smartsheet templates monitor both the planned and actual start dates for each task. The actual dates are linked to Smartsheet's resource allocation feature. I am trying to leverage Smartsheet to give each Team Leader a View of their team's planned and actual allocation. In short, I need exactly the resource view for my planned columns as well. All the sheets that I need information from are in a single workspace.

Any help would be appreciated.

Sean Morgan

Hello @Niyati Kumar

Currently, the Resource View does not allow all this data to be entered into a single view. Please can you raise an Enhancement Request using the quick links on the side of this feature. Great idea and I can totally see the use case scenario for it!

Alternatively, you could create a Report to collect all this data, and then share the Team Leaders to this Report, or share it to them as a PDF. See more on this here: https://help.smartsheet.com/articles/522214-creating-reports

Please reach out if you have any questions.

Regards

Sean

tammy.schmitz23386

@Niyati Kumar - Would you be willing to share how you're tracking planned vs. actual work in your templates? I am also looking for better resource management reporting for my teams.

Niyati Kumar

@Sean Morgan--Thanks. I have raised a Product Enhancement Request for this. About the link that you shared --is this somthing I can do on an Enterprise license or is this part of the 10,000ft offering? I have tried using the Reports feature (I'm on the Enterprise plan) to achieve my requirement, but I've not been able to collect this data in an easily readable format.

Genevieve P.

Hi @Niyati Kumar

I believe Sean meant to link to the article on Reports for Smartsheet, versus the one for 10,000ft. I've adjusted the link above to reflect the correct data. As a licensed user on an Enterprise plan you will be able to create Reports.

If it's not displaying how you would like, it may be helpful for us to see screen captures of your data & built Report so we can suggest some alternatives (but please block out any sensitive data!).

Thanks!

Genevieve

Niyati Kumar

@Genevieve P , thank you. I have used the reports feature for other use cases, but I haven't been able to do that successfully for this scenario.

Here's what our project plan templates look like. What I'd like is for us to have a report where each team member's total monthly allocation (sum of the target hours) across all sheets is reported based on the Start and End dates (these are linked to the predecessors). That way their managers can accurately plan capacity.

Genevieve P.

Hi @Niyati Kumar

Thanks for clarifying further! Reports are great for showing specific rows based on criteria across multiple sheets (for example, all rows that are assigned to a specific user so you can see what they're working on). However for what you're looking to do, you'll want to set up a helper sheet to perform formula calculations.

You can use a cross-sheet SUMIFS formula to look into a sheet and SUM together the number in the Target Hours column, for if the End Date is a specific month, based on the person it's assigned to.

Where it will get tricky is if the task spans over two months... you may need to break these down in the sheet to be part one of the task in the first month, and part 2 of the task in the second month.

Here's an example of how you might set up your sheet to build a formula like this:

=SUMIFS({Target Hours}, {Assigned To}, [Assigned To]@row, {Start Date Column}, MONTH(@cell) = MONTH(Month@row))

If you have multiple sheets, you can add together the SUM from each sheet:

=SUMIFS({Target Hours Sheet 1}, {Assigned To Sheet 1}, [Assigned To]@row, {Start Date Column Sheet 1}, MONTH(@cell) = MONTH(Month@row)) + SUMIFS({Target Hours Sheet 2}, {Assigned To Sheet 2}, [Assigned To]@row, {Start Date Column Sheet 2}, MONTH(@cell) = MONTH(Month@row))

You could then use the chart above in a Chart Widget in a dashboard, perhaps with different report views below.

Let me know if this makes sense, and if you think it will work for what you're trying to do! You may also want to take a look at the 10,000ft platform for resource management.

Niyati Kumar

Thanks, @Genevieve P , I had been trying to work out a similar formula and using SumIFs and Month@cell. I did come to a similar conclusion. For now I have included tasks across months in the last month to get the most accurate info. Yes, I have gotten in touch with your team about 10,000ft and will have a conversation about this next week. Thanks again.

Genevieve P.

No problem! Glad you sorted out something for now 🙂