Resource Forecasting formula

David Mason

Dear all,

I hope someone can help me; I have a large sheet with a row per resource and a set of 4 columns repeating for each week of the year. The first column of the 4 has a week commencing date; this is dynamic based on a start date I enter into a reference cell. The second column in the repeating columns derives the month and year from the week commencing date. The following two columns have the planned hours and the actual hours.

The columns repeat to allow for 52 weeks.

The help I need is if there is a formula that will allow me to capture the total hours planned per month from the original sheet on a summary sheet per resource. I have replicated the resources in the first column of the summary sheet and added the month and year to the 1st row on the summary sheet.

I appreciate that this may be a big ask, but any support would be appreciated.

Paul Newcome

Are you able to provide screenshots for reference (with sensitive/confidential info blocked out)?

David Mason

Hi Paul

I hope this helps, the screenshot has another column in it but I just want to be able to summarise the planned and actual by month, the first column slightly off shot contains the start date which then allows the data to be dynamic from one project to the next

Screenshot 2022-05-26 132153.png

the second screenshot is the summary sheet I am trying to populate

hope this helps

Genevieve P.

Hi @David Mason

You could manually select the 4 columns to SUM for each month column:

=SUM(SUMIFS({Column 1}, {Roles Column}, Roles@row), SUMIFS({Column 2}, {Roles Column}, Roles@row), SUMIFS({Column 3}, {Roles Column}, Roles@row), SUMIFS({Column 4}, {Roles Column}, Roles@row))

However one sheet can't have more than 100 distinct {references} so you may need to break this out into two sheets.

Otherwise, I can't personally think of a way to do this dynamically without rearranging your source sheet.

If I was to rearrange, I would set up the source sheet so that each column is a Role... 3 columns per role with the Planned, Actual, and Variance.

Then the Hierarchy is the week and month rows. This would allow you to only have one Date column to identify the start of each week for those rows, then using SUM(CHILDREN()) you could have the SUM of those weeks in another parent row, the Month. Then we could create a Report using only the Month rows instead of a second sheet.

I hope this helps.

Genevieve

David Mason

@Genevieve P. thank you for your recommendation - I have this type of format currently, due some challenges we have with this format I was hoping to redesign it in the way I have described but it does look like this may not be possible.