Forum Discussion
Working with dates
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.
Hi again Sergei
Is there a way to make this work if the budget data is not in a convenient column?
I have 18 different product groups and the budget is currently arranged Month January, Month February etc. so I'm guessing the index($b$2:$b$13 in your example won't be the right terminology. I need to be able to pick up January line 1, February line 13, March line 25 etc. Do you see what I mean?
If by product when it could be SUMIFS() of budgets for all month less or equal than current and for given product.
To be more concrete it's desirable to have the sample with more or less actual data structure.
Hi Sergei
That is excellent, thank you. It's way beyond my skill level no wonder I was struggling to solve it.
Many thanks
Emmcee