Rolling months based on date

Hi Folks,

My challenge is the following:

I would like the spreadsheet values in F8 to F19 to change based on the date I place in cell B2.

So if I select January 2014 for example then the value in B19 changes to the previous month ( December 2013) and the value would be that in calculated in AP3 would appear in F19

and if I select February 2014 then the value of AP3 would be place in the cell F18.

I think there might be simpler way of doing things and look forward to your inputs

small steps...

1. change B19 to this...
=EDATE($B$2,-1)
Then change B18 to this, and copy upwards...
=EDATE(B19,-1)

2. In F8, copy this down...
=INDEX($AE$4:$AP$4,MATCH(TEXT(B8,"mmm"),$AE$3:$AP$3,0))

edit: Hoe gaan dit daai kant van die groot water? SA ex-part here from PE and Klerksdorp

Thanks for the prompt reply.

I have made the changes suggested.

The values in column F do no change according to the month change.

Good to hear from a fellow SA citizen - dit gaan goed dankie

OK Im not really sure what you are trying to do here (it's getting late and maybe Im not reading stuff right = moeg geploeg lol), but hile trying to figure things out, I have simplified some formulas for you.

AE4=SUMPRODUCT(--(TEXT($B$24:$B$227,"mmm")=AE$3),$F$24:$F$227)

If you want the values in AE4:AP4 to change when you change the date in B2, then you need to use what I suggested for the dates in B19 upwards there as well, so that they change...
AP3=EDATE($B$2,-1)
AO3=EDATE(AP3,-1) copied back
If you do that, then you can replace the sumproduct with this, copied across...
=SUMIF($B$24:$B$227,AE$3,$F$24:$F$227)

A word of advice - when working with dates, try and keep all dates as real dates, not text dates. "Jan" is text, and excel doesnt really know that it is a month name/date, but 1/1/2014 is a real date, and excel has a bunch of formulas that can be used based on that. So for instance, in your SUMPRODUCT(), you had to build in a function to convert a date to text TEXT(B8,"mmm"), but if they were all dates, that would not be needed.

Anyway, having said all that, try applying the changes I have suggested, then see if you can adapt them to what you need. If you still have a problem, shout again - with samples of what you are changing, and what you expect, and I will take another look later.
*bed calling now lol*

Thanks for the good advice.

I continued with your previous post and made the following change

=INDEX($H$3:$S$3,MATCH(TEXT(B16,"mmm"),$AE$3:$AP$3,0))

and this seems to be working for the top section, now I need to make it work for the lower sections in the worksheet.

Thanks for the offer of continued support.

Lekker slaap