Autofill monthly budget based on contract period

[Tai Idrus]

I wanted to make a contract monitoring dashboard using pivot table. I have hundreds of contract with different contract period.

I've created a sample table to illustrate my actual table but with only 5 contract. Please note that the column should extends until end of 2023 for this sample.

How do I automate this template to have the month column to be automatically filled based on the contract duration?

Appreciate if anyone can help me solve this dilemma.

Contract	Start Date	End Date	Contract Duration (Month)	Contract Sum	Monthly Budget	01-2022	02-2022	03-2022
Contract 1​	01/01/2022	01/05/2022	4	$ 10,000.00	$ 2,500.00
Contract 2​	01/03/2022	03/01/2023	10	$ 250,000.00	$ 25,000.00
Contract 3​	01/07/2022	31/12/2023	17	$ 150,000.00	$ 8,823.53
Contract 4​	01/05/2022	01/10/2023	17	$ 135,000.00	$ 7,941.18
Contract 5​	01/01/2023	31/12/2023	11	$ 1,000,000.00	$ 90,909.09

 

[6StringJazzer]

When asking a question like this always give an example of what the result should look like. I am taking a guess as to what you need.

Your data format is working against you. It would be easier if your headings were actual dates, instead of text (01-2022 is text, not a date). The formulas have to use cell references (G$1) instead of structured table references (Table1[[#Headers],[02-2022]]) for that reason.

See attached.