Background: our business has Building Managers (BMs) that we provide to a multitude of our clients on a contracted hour/week basis.
The BMs have a fixed weekly work hour limit of 38 hours per week.
Our various clients have standard attendance hours per week that do not align with the weekly BM hour limit. This leaves us with capacity to use elsewhere.
We can, and do monitor this via rollups and various other methods (no help needed here)
We also have a healthy pipeline of upcoming business (and BM hour requirements) that is also logged in another sheet. We are trying to predict our resource requirements (BM hires) in the future so that we employ staff at the right time.
We can currently do this however the sheet is set up with a header row (to capture the current surplus, calculated via rollup in a summary field) and then we use a row formula on row 2 and drag it down to provide the progressive balance of available hours on new business that require BM hours because it is reliant on the previous row’s progressive balance.
On a regular basis we must swap the order as buildings get delayed etc and of course this breaks the formula, and we must add it back in to row 2 (as it is usually the next buildings coming on that are affected) and drag down.
This is very much an Excel type setup, and we of course cannot set the formula as a column formula.
If anyone has dealt with this before and resolved it to become a column formula, I would love to find out how it can be done.
