Feb 11 2020 04:31 AM
Hi all
I have a spreadsheet which I add sales figures to each month. On this spread sheet I have a tab with all my budget figures for each month and a tab with product analysis. On the products tab I want to be able to automatically update the budget cell when I add a new months sales figures. The sales lines will be different each month so I can't use that criteria to create a formula I was think of using the date somehow but the start and end date for each month won't necessarily be the first and end of each month so again I can't use that as a criteria. Is there a way I can use the month perhaps?
Feb 11 2020 07:14 AM
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
Feb 11 2020 07:19 AM
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.
Feb 11 2020 08:32 AM
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
Feb 11 2020 09:00 AM
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.
Feb 11 2020 01:57 PM
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.
Feb 11 2020 11:40 PM
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
Feb 12 2020 01:03 AM
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?
Feb 14 2020 01:39 PM
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.
Feb 18 2020 07:20 AM
Hi Sergei
I understand. Thank you for all your help it's been invaluable and I'm pleased to say that it's working perfectly.
Many thanks