I'm building a spreadsheet where I have 3 variances that I am concerned with:
1. Current Month Better/(Worse) Prior Month - I use an offset formula to look at the two specific columns
2. Current Month Better/(Worse) Prior Year - I use the same formula as above but point to the same month in the previous year
3. Current Month Year-to-Date Better/(Worse) Prior Year-to-Date
It is this third formula that I am having trouble with. Currently I copy the current month column and insert to the left. That column does a vLookup on another sheet to get the correct values. I need the YTD variance column to continually grow each month so that it captures Jan to May 2014 less Jan to May 2013. Then the next month as I insert the new month's column I need that formula to "automatically" update to calculate Jan to Jun 2014 less Jan to Jun 2013.
Cell A1 has the first day of the current month so I was thinking of using the "Month" formula to convert that to a growing variable which equates to the number of columns to use. Then use that in conjunction with the offset formula. But I'm not sure how to do this. I've attached a sample spreadsheet to assist you. Thanks in advance!
Bookmarks