Dynamically Adjusting Remaining Values

Hello, I have an Excel workbook with two sheets: "Time Allocated" and "Time Spent". At the moment I have the same data in each sheet - in column A I have a list of unique project reference codes;...

excel

Excel on Mac

Formulas and Functions

HansVogelaar
Jun 19, 2023
User9573053

Thanks. See the attached demo workbook. Please note that the formulas in columns F, G etc. are different from those in column E.
demo.xlsx16 KB

User9573053

Copper Contributor

Jun 16, 2023

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

HansVogelaar

MVP

Jun 17, 2023

User9573053

Could you provide one or two rows from both sheets with values filled in, showing the expected result?

Forum Discussion

Dynamically Adjusting Remaining Values