How to automatic rolling data based on the change of the month

Hi Guy,

Currently I'm creating a rolling 12 month forecast in excel. I'm using the sumif formula to pick up the data in the column that matched to the select month:

SUMIF($A:$A,$T10,OFFSET('RJV FY2013 Op B'!$A$1,0,COLUMN('Rolling Cashflow'!D$8))).

However, if I change the date in D8 from Jun12 to July12, the formula is still picking up the Jun12 data instead of Jul12. How should I fix up this?

Thanks,
YS

hi YS, you can use INDEX & MATCH instead:
=INDEX('RJV FY2013 Op B'!$C$6:$Q$6,0,MATCH('Rolling Cashflow'!D8,'RJV FY2013 Op B'!$C$3:$Q$3,0))

i see that your figures are simply taking 1 month & not accumulative, hence you don't need SUMIF

would you mind uploading an eg? not sure what your criteria is like. so the summary tab only shows 1 line as "Staff Cost" & you need to sum up the 3 costs found in the source data?

can the naming for your column B in "Rolling Cashflow" & the other worksheet be synchronized? Staff Costs for eg, is named the same for both worksheets. so can A9:A11 be named "Capex Project" in worksheet "RJV FY2013 Op B" too? then it's easier to search for it using the formula, rather than presetting the formula to read only 2 rows for Staff Costs & 3 rows for Capex Project. After naming it "Capex Project", this formula should work for both. Place this in cell D12:

=SUMPRODUCT(('RJV FY2013 Op B'!$A$6:$A$11='Rolling Cashflow'!$B12)*(OFFSET('RJV FY2013 Op B'!$B$6,0,MATCH(D$8,'RJV FY2013 Op B'!$C$3:$Q$3,0),6)))

but i have a feeling your detailed file may need more than this. let me know if so & upload the file.