Scheduling for Resource Availability
Hello,
I have a sheet for users to enter requests for our quality department. They chose a type of task they need, i have a VLookup assigning how many hours to assign to that row based on the task. That all works fine. But I need to find away to factor in the staffing restraints for each day and push some tasks out if too many others are already due that day. I have created a grid but can't use a gantt b/c I'm using formulas for duration and start/end dates.
I have a quantity of hours available for tasks per day - and what i want is for SmartSheets to be able to look at the date its available to work on, consider how many "unassigned hours" are available for that day, and then if unassigned hours>hours needed for task, then return that date under an Assigned column, and then reduce the number of hours available for that day. If Day 1 doesn't have enough unassigned hours, then look at Day 2 -etc.
I created the columns above to do that - it refences this separate grid below that sums the items with the "planned date" for each day along with the available hours.
The tricky part is turning out to be that i want it to reduce the number of "unassigned" hours without creating a circular reference, and that i want it to consider each row separately - currently it seems to be considering all things due on a date together (summed).
Trying to allocate work based on available hours doesn't seem like it would be a unique problem - has anyone else figured out a way to do this? Hopefully a simpler way? Would the Resource Management Add-On do this for me?
Thanks in advance!
Answers
-
Hi @Lynn234,
As you mention, the best resource to work with and track time is Resource Management. This would also allow you to easily create different types of Reports that you can use to get comprehensive information about how each project/task is staffed.
A possible alternative to this could also be Resource views within the Legacy Resource Management. But as the name indicates, there are plans for this tool to be discontinued sometime in the future, although there isn't yet a confirmed date for this to occur. With this in mind, the Resource Management app would be my best recommendation if you are considering to acquire it for your organization.
If you'd like to continue to use your own solution and yet would need help with the formula producing CIRCULAR REFERENCE errors, please include additional captures where this is displayed within the context of your sheet so that we can continue to advise.
I hope that these ideas can be of help.
Cheers!
Julio
