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.
Could you attach a small sample workbook with some dummy data, or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
I can't share the workbook due to workplace settings sorry but here's the data, the same in both sheets - Time Allocated would be where I need the formula, Time Spent would be where I manually input the 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 | |||||||||||||||||||
| 5 | 7 | Jun-23 | Oct-23 | |||||||||||||||||||
| 6 | 2 | Jun-23 | Jun-23 | |||||||||||||||||||
| 7 | 2 | Jun-23 | Jun-23 | |||||||||||||||||||
| 8 | 11 | Jun-23 | Nov-23 | |||||||||||||||||||
| 9 | 16 | Jun-23 | Aug-23 | |||||||||||||||||||
| 10 | 6 | Jun-23 | Nov-23 | |||||||||||||||||||
| 11 | 10 | Jun-23 | Dec-23 | |||||||||||||||||||
| 12 | 12 | Jun-23 | Dec-23 | |||||||||||||||||||
| 13 | 12 | Jun-23 | Dec-23 | |||||||||||||||||||
| 14 | 1 | Jun-23 | Jun-23 | |||||||||||||||||||
| 15 | 4 | Jun-23 | Jun-23 | |||||||||||||||||||
| 16 | 1 | Jun-23 | Jun-23 | |||||||||||||||||||
| 17 | 1 | Jun-23 | Jun-23 | |||||||||||||||||||
| 18 | 1 | Jun-23 | Jun-23 | |||||||||||||||||||
| 19 | 0.5 | Jun-23 | Jun-23 | |||||||||||||||||||
| 20 | 3 | Jun-23 | Jun-23 | |||||||||||||||||||
| 21 | 2 | Jun-23 | Jun-23 | |||||||||||||||||||
| 22 | 10 | Jun-23 | Jun-23 | |||||||||||||||||||
| 23 | 1 | Jun-23 | Jun-23 | |||||||||||||||||||
| 24 | 3 | Jun-23 | Jun-23 | |||||||||||||||||||
| 25 | 1 | Jun-23 | Nov-23 | |||||||||||||||||||
| 26 | 0 | Jun-23 | Dec-23 | |||||||||||||||||||
| 27 | 0 | Jun-23 | Dec-23 | |||||||||||||||||||
| 28 | 0 | Jun-23 | Dec-23 | |||||||||||||||||||
| 29 | 12 | Jun-23 | Dec-23 | |||||||||||||||||||
| 30 | 12 | Jun-23 | Dec-23 | |||||||||||||||||||
| 31 | 1 | Jun-23 | Jun-23 | |||||||||||||||||||
| 32 | 3.5 | Jun-23 | Sep-23 | |||||||||||||||||||
| 33 | 1 | Jun-23 | Jun-23 | |||||||||||||||||||
| 34 | 0 | Jun-23 | Aug-23 | |||||||||||||||||||
| 35 | 0.5 | Jun-23 | Jun-23 | |||||||||||||||||||
| 36 | 8 | Jun-23 | Jun-23 | |||||||||||||||||||
| 37 | 2.5 | Jun-23 | Jun-23 | |||||||||||||||||||
| 38 | 2 | Jun-23 | Jun-23 | |||||||||||||||||||
| 39 | 1 | Jun-23 | Jun-23 | |||||||||||||||||||
| 40 | 2 | Jun-23 | Jun-23 | |||||||||||||||||||
| 41 | 22.5 | Jun-23 | Jun-23 | |||||||||||||||||||
| 42 | 4.5 | Jun-23 | Sep-23 | |||||||||||||||||||
| 43 | 3 | Jun-23 | Sep-23 |
Could you provide one or two rows from both sheets with values filled in, showing the expected result?
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.