Working with dates

[emmcee]

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?

[#2]

@emmcee 

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

[#3]

@Sergei Baklan 

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.

[#4]

@emmcee 

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

[#5]

@Sergei Baklan 

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.

[#6]

@emmcee 

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.

[#7]

@Sergei Baklan 

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

[#8]

@Sergei Baklan 

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?

[#9]

@emmcee 

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.

[#10]

@Sergei Baklan 

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

[#11]

@emmcee , you are welcome