I am using a dynamic chart that shows a series of values for a 12 month period on a rolling basis. The OFFSET formula I use is
=OFFSET('2.1'!$A$2,0,COUNTA('2.1'!$A:$A)-1,1,-MIN(chtLen,COUNTA('2.1'!$A:$A)-1))
with the chtLen being 12
This works well for data sets that have the dates in one column. When I add a new column the chart will be updated so that the latest 12 months are included in the chart.
My problem is that I have a second set of data that has the months listed in a row. I want to make a new, similar chart and tried to change the formula to make the chart categories move dynamically in a row as opposed to down the column:
=OFFSET('2.1'!$A$2,0, COUNTA('2.1'!$A:$A)-1, MIN(chtLen,COUNTA('2.1'!$A:$A)-1))
It failed miserably.
Any ideas appreciated
Bookmarks