Formula that will update values as actuals are entered

[Deleted]

Hi,

I have a formula query for everone out there.

I have a budget which is pulled from a separate tab laying out the dates and values (see columns A-F)

This is then spread evenly across the period of the expenditure of the project.

This is a budget however, Then as invoices come in, I am looking to hardcode in the actual invoice value. (e.g what is in red in cells H15:O18.

What I am looking for is a way to update the formula, so that when I enter an actual, the remaining budget e.g. cells P15:V18 spread the remaining budget across the remaining period.

e.g. P15 should calculate to be 50,000 and P17:T17 should be 40,000 each.

The current formula is:

=AND($B$5<=H13,$C$5>=H13)*$F$5/$D$5

B5 = cost start date

C5 = cost finish date

H13 = actual date

F5 = total budget

D5 = number of periods cost is spread over

Any help will be appreciated

[#2]

Hi you can record a macro on that. Then bind it to a button In your workbook. If it’s in an excel online file, make a Flow to get this stuff done, Eva

[#3]

Hi,

You may try

=($F5-SUMIFS($H15:O15,$H$13:O$13,">="&$B5,$H$13:O$13,"<="&$C5))/(YEARFRAC(P$13,$C5)*12+1)*($B5<=P$13)*($C5>=P$13)

Please see in attached file.