Data Validation Lists & showing Month over Month performance

[hilpup24]

Hi All! Hoping someone can explain how I can achieve showing month over month performance while also using a monthly dropdown menu. Basically I have data set of revenue that may go up or down each month. In my report I have a dropdown menu to view a particular month or a YTD total for several categories. One category in particular is going to be dependent on the month prior and that is where I am getting stuck.

These are the formulas I use to show a normal category....now I just don't know how to set up a formula to base the revenue differential from the previous month. I really hope I have explained this correctly....

=IF($D$4="January",SUMIFS(Data!$V:$V,Data!$Z:$Z,'2013 FA Financial Scorecard'!$B32,Data!$BE:$BE,201301),SUM(IF($D$4="February",SUMIFS(Data!$V:$V,Data!$Z:$Z,'2013 FA Financial Scorecard'!$B32,Data!$BE:$BE,{201301,201302}),SUM(IF($D$4="March",SUMIFS(Data!$V:$V,Data!$Z:$Z,'2013 FA Financial Scorecard'!$B32,Data!$BE:$BE,{201301,201302,201303}),SUM(IF($D$4="April",SUMIFS(Data!$V:$V,Data!$Z:$Z,'2013 FA Financial Scorecard'!$B32,Data!$BE:$BE,{201301,201302,201303,201304}),SUM(IF($D$4="May",SUMIFS(Data!$V:$V,Data!$Z:$Z,'2013 FA Financial Scorecard'!$B32,Data!$BE:$BE,{201301,201302,201303,201304,201305}),SUM(IF($D$4="June",SUMIFS(Data!$V:$V,Data!$Z:$Z,'2013 FA Financial Scorecard'!$B32,Data!$BE:$BE,{201301,201302,201303,201304,201305,201306})

Any help anyone can provide would be great. Thank you all in advance!

[DonkeyOte]

@hilpup24,

I am guessing you've since moved on from the above, however, if of interest I think you could simplify the YTD formula in your previous post to just:

Please Login or Register  to view this content.

above assumes (per existing inline array) that Col BE holds numbers.

The above construct should show you how you can dynamically build the month requirement from the dropdown ? e.g Prior Month

Please Login or Register  to view this content.

obvious limitation in terms of January prior given lack of Year variable (do you have one ?)

Note: if preferred you can move the 2013 prefix into the format string, eg:

Please Login or Register  to view this content.