Forum Discussion
Dynamically Adjusting Remaining Values
Thanks. See the attached demo workbook. Please note that the formulas in columns F, G etc. are different from those in column E.
Sure thing! So on "Time Allocated", before anything is entered into "Time Spent" the allocations for Projects 4 & 8 should show like this (I've included less rows for ease):
| Project Ref | Total Days | Project Month Start | Project Month End | Jun-23 | Jul-23 | Aug-23 | Sep-23 | Oct-23 | Nov-23 | Dec-23 | Jan-24 | Feb-24 | Mar-24 | Apr-24 | May-24 | Jun-24 | Jul-24 | Aug-24 | Sep-24 | Oct-24 | Nov-24 | Dec-24 |
| 1 | 19 | Jun-23 | Jun-23 | |||||||||||||||||||
| 2 | 6 | Jun-23 | Jun-23 | |||||||||||||||||||
| 3 | 4 | Jun-23 | May-23 | |||||||||||||||||||
| 4 | 4 | Jun-23 | Jul-23 | 2 | 2 | |||||||||||||||||
| 5 | 7 | Jun-23 | Oct-23 | |||||||||||||||||||
| 6 | 2 | Jun-23 | Jun-23 | |||||||||||||||||||
| 7 | 2 | Jun-23 | Jun-23 | |||||||||||||||||||
| 8 | 11 | Jun-23 | Oct-23 | 2.2 | 2.2 | 2.2 | 2.2 | 2.2 | ||||||||||||||
| 9 | 16 | Jun-23 | Aug-23 |
Then on "Time Spent", if I enter this data:
| Project Ref | Total Days | Project Month Start | Project Month End | Jun-23 | Jul-23 | Aug-23 | Sep-23 | Oct-23 | Nov-23 | Dec-23 | Jan-24 | Feb-24 | Mar-24 | Apr-24 | May-24 | Jun-24 | Jul-24 | Aug-24 | Sep-24 | Oct-24 | Nov-24 | Dec-24 |
| 1 | 19 | Jun-23 | Jun-23 | |||||||||||||||||||
| 2 | 6 | Jun-23 | Jun-23 | |||||||||||||||||||
| 3 | 4 | Jun-23 | May-23 | |||||||||||||||||||
| 4 | 4 | Jun-23 | Jul-23 | 2 | ||||||||||||||||||
| 5 | 7 | Jun-23 | Oct-23 | |||||||||||||||||||
| 6 | 2 | Jun-23 | Jun-23 | |||||||||||||||||||
| 7 | 2 | Jun-23 | Jun-23 | |||||||||||||||||||
| 8 | 11 | Jun-23 | Nov-23 | 2.2 | ||||||||||||||||||
| 9 | 16 | Jun-23 | Aug-23 |
There shouldn't be any change to the data in "Time Allocated" as the correct allocated time was used, so future month allocations remain the same. But, if I enter the data below into "Time Spent":
| Project Ref | Total Days | Project Month Start | Project Month End | Jun-23 | Jul-23 | Aug-23 | Sep-23 | Oct-23 | Nov-23 | Dec-23 | Jan-24 | Feb-24 | Mar-24 | Apr-24 | May-24 | Jun-24 | Jul-24 | Aug-24 | Sep-24 | Oct-24 | Nov-24 | Dec-24 |
| 1 | 19 | Jun-23 | Jun-23 | |||||||||||||||||||
| 2 | 6 | Jun-23 | Jun-23 | |||||||||||||||||||
| 3 | 4 | Jun-23 | May-23 | |||||||||||||||||||
| 4 | 4 | Jun-23 | Jul-23 | 1 | ||||||||||||||||||
| 5 | 7 | Jun-23 | Oct-23 | |||||||||||||||||||
| 6 | 2 | Jun-23 | Jun-23 | |||||||||||||||||||
| 7 | 2 | Jun-23 | Jun-23 | |||||||||||||||||||
| 8 | 11 | Jun-23 | Nov-23 | 2.2 | 4 | |||||||||||||||||
| 9 | 16 | Jun-23 | Aug-23 |
I've gone under the allocation on Project 4 in June, and as-expected on Project 8 in June, but then I've gone over in July. I would then want the data in "Time Allocated" to dynamically update to:
| Project Ref | Total Days | Project Month Start | Project Month End | Jun-23 | Jul-23 | Aug-23 | Sep-23 | Oct-23 | Nov-23 | Dec-23 | Jan-24 | Feb-24 | Mar-24 | Apr-24 | May-24 | Jun-24 | Jul-24 | Aug-24 | Sep-24 | Oct-24 | Nov-24 | Dec-24 |
| 1 | 19 | Jun-23 | Jun-23 | |||||||||||||||||||
| 2 | 6 | Jun-23 | Jun-23 | |||||||||||||||||||
| 3 | 4 | Jun-23 | May-23 | |||||||||||||||||||
| 4 | 4 | Jun-23 | Jul-23 | 1 | 3 | |||||||||||||||||
| 5 | 7 | Jun-23 | Oct-23 | |||||||||||||||||||
| 6 | 2 | Jun-23 | Jun-23 | |||||||||||||||||||
| 7 | 2 | Jun-23 | Jun-23 | |||||||||||||||||||
| 8 | 11 | Jun-23 | Oct-23 | 2.2 | 4 | 1.6 | 1.6 | 1.6 | ||||||||||||||
| 9 | 16 | Jun-23 | Aug-23 |
This has then decreased the allocation for Project 4 in June and increased the remaining allocation for July. On Project 8, it's kept June's allocation the same as it was, increased July's and subsequently reduced the available allocation for August-October.
Hope that makes sense, thanks Hans 🙂
Thanks. See the attached demo workbook. Please note that the formulas in columns F, G etc. are different from those in column E.
- I think this is it, thank you so much Hans! I'll do some more testing 🙂