I have a weight spreadsheet which has been running for the past 3 years and have
just now set up a dynamic weight chart covering the last 100 days based on
information from a Jon Peltier paper on dynamic charts.

The data in the Weight workbook is set up as follows:-
Col A - date
Col B - AM weight
Col C - 7day AM weight average

The original formula for the X axis data was
ChrtCats=offset(Weight!$A$1,counta(Weight!$A:$A)-1,0,-ChrtLen,1)
where ChrtLen =100 and the Y axis data is given by
ChrtValA=offset(ChrtCats,0,1).

This formula had to be modified to
ChrtCats=offset(Weight!$A$13,counta(Weight!$B:$B)-1,0,-ChrtLen,1)
because
1. the end x axis date was wrong presumably because there are 10 blank entries
in Column B and changing to Weight!$A$13 fixed that, and
2. Column A has dates set up extending to end of 2006 hence the
Counta(Weight!$B:$B) change as Column B only has data to the current date.

What puzzles me is why the Weight!$A$1 had to be changed to Weight!$A$13 to make
the X axis dates end on the current date?

I could understand it if this was changed to weight!$A$11 - i.e. 1 + the number
of blank cells in column B and that was what I originally tried. By trial and
error I finally settled on $A$13.

Can anyone explain why the figure 13 works? Even better can anyone give me a
correction to the ChrtCats formula to allow for future blank entries in column B
so that I don't have to fiddle with the formula each time another blank cell
happens?

--

Cheers . . . JC