We have a resource availability tab, with an employee as each ‘parent’ item. I was hoping to add subitems to each item based on requests from project managers to make use of that employee. I have added number columns to be used as the hours / days requested per week - each column represents one week and so if I need that person for 1.5 days, I would add a subitem, fil in the relevant info and change a status column to requested, which pings the approver a note.
Either listed in the parent item, or as another sub-item row, we would add the availability of that employee for each week so that we can easily see if they have been over subscribed or requested. I could create columns in the parent item to match the number columns in the sub-item, or add a status column in the sub-item which either says ‘request’ or ‘availability’ to show which is which.
Is there a way to then calculate the net position per week (within a column) so that we can easily see when there is an over demand?
A simple way to do this would be to create the summary on the parent item, but that would rely on people remembering to put a negative sign in front of the demand required so that it calculated properly i.e. available 4 days, -1.5 days requested. This is an easy fix, but is fraught with danger.
Ideally I would have a formula column on the parent that would be configured to take any ‘demand’ subitems away from the ‘availability’ subitems.
The columns with the dates are number columns to allow for the demand in numbers.
Simple pic below:
