Home

Enter typical days of months income/expenditure to flow into rolling forecasts

Highlighted
redwookie99
Occasional Visitor

Hi.  I have a personal budget where I have set it up so I have a input sheet that has a column for each day which includes details of income or expenses for each day (365 columns for each year).  But I have to populate this sheet manually.  I can think link this to a summary sheet where I can forecast what my current account balance will be by summing all income and expenses between (today) and my selected date (with reference to the input sheet).  That formula is below where 'Detail' is the input sheet , I13 is the forecast date and F13 is just my current balance .

 

=(SUMIFS(Detail!B37:NB37,Detail!B36:NB36,">"&TODAY(),Detail!B36:NB36,"<="&$I$3))+F13

 

What I would like to be able to do is to remove the manual entry required on the input sheet and have a simple input sheet with details of the day of the month that income/expenditure occurs and for this to flow into my existing formula for the forecast.

 

Any ideas?  Thanks in advance

1 Reply
You may modify the layout of your input sheet in such a way that Column A:D are labelled Date, Income, Expense, and Balance, respectively. In D2, copied down rows, the formula is: =SUM(D1,B2,-C2)
Thereafter, you only need to perform a simple VLOOKUP to determine your Balance as at any given date.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies