Quote:
Originally Posted by Ryan P
It seems like this answer should be so easy, but I just can't figure it out.
Here's my situation:
On one worksheet, '2009' I have a series of volumes Jan through December with totals at the end.
On a more recent worksheet, '2010' I have the same exact worksheet, and I'm filling it in as the months go by (currently in July). However, I want to add a column for Year-to-date comparison, and I don't want to have to go to the previous worksheet and add up the months I need.
So, is there a formula that would automatically add the correct months in '2009' based on the data for that month being entered in '2010', e.g. I fill in August in '2010' and on the '2009' worksheet it adds Jan-Aug. etc.
Thanks for your help and time. I just can't figure this one out.
|
Let's say your table looks like this:
2009 Worksheet:
A B C D E F
JAN FEB MAR APR MAY JUN
1 1 2 3 4 5 6
2010 Worksheet:
A B C D E F
JAN FEB MAR APR MAY JUN
1 10 9 8 7 6
For your Last Year's YTD:
=SUM(OFFSET('2009'!A1,0,0,1,COUNTA('2010'!A1:F1))
For your Current YTD:
=SUM('2010'!A1:F1)
NOTE: If a month has a null value, do not leave it as blank - instead, place a zero.