Assistance rolling up data to a master sheet

Lindy Davidson

Hello,

I’m trying to create a roll up for multiple resources across multiple sheets. I would like to have data roll up like how it looks in the Resource Allocation, but I’d like to view allocations in hours rather than percentages.

I've attached an example of a project schedule in SmartSheet that we use.

I thought that it might work to add a SUMIF formula to the bottom of each sheet and then link that data onto a master sheet. However, I cannot seem to separate data by weeks. I need to look at the upcoming hours per resource by project by week for the next four to six weeks, and grouped by weeks.

I've attached an example of what our resource hour tracking looks like from Excel. All of the hours per project per week would come from our individual project schedules.

Can someone point me in the right direction on how to create a report/sheet mimicking the resource allocation currently available but using duration?

Thanks!

Shaine Greenwood

Hi Lindy,

It looks like you're wanting to get total effort in hours from a daily effort, then roll this up to a master sheet for each week of your project.

One way of doing this is to split your project up into weeklong phases, you can then multiply each phase by your resource hours: =Duration1 * [Effort (hours)]1

You'll have multiple rows per the weekly phases of each project, but it's difficult to create a formula that separates the weeks out between two dates (start and end date of your project).

Check out our help article on Creating Formulas for examples of formula syntax in Smartsheet. 

You can find our complete list of available functions in Smartsheet here.

Hope this helps steer you in the right direction.