Forum Discussion
Working with dates
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.
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?