Forum Discussion
Working with dates
How do you separate one month from another? You say about
start and end date for each month won't necessarily be the first and end of each month
but I see no dates in your sample
Hi Sergei
You're correct I left that particular tab off the example to make the file smaller but in reality the Products tab is fed from a Sales tab which has all the sales for any given month.
Okay, thank you. But that doesn't answer on do you have any dates and if you have when there. Taking values from the Budget tab could depend on dates and the way of using them
Hi Sergei
Attached is an abridged version of the spreadsheet. This is a Y.T.D. file so when I add Februarys sales figures to the sales tab I'm looking for a way to automatically update the budget figure to add Feb's budget to Jan's budget and so on.
First you may calculate current month for YTD sales list as MONTH(MAX(Sales!$A:$A))
It returns 1 for Jan, 2 for Feb, etc.
Range of budget figures from the beginning of the year (Jan) till current month could be calculated as
$B$2:INDEX($B$2:$B$13, <month>). For example for Feb that will be $B$2:INDEX($B$2:$B$13,2) which is equivalent of $B$2:$B$3. Same for other months. All what you need now is to sum that range.
Combine all together, for Home budget
=SUM(Budget!$B$2:INDEX(Budget!$B$2:$B$13,MONTH(MAX(Sales!$A:$A))))Please see in attached file.