I'd look through Jon Peltier's Dynamic Chart link to see if anything there is
useful.

http://www.peltiertech.com/Excel/Charts/Dynamics.html

"Scott Hamilton" wrote:

We have a chart that needs to show results ofr a series of years. The number
of years varies based on the age of the client. Is there a way to automate
excel to look for the column containing the appropriate last year and fill
the source data with all columns up to that year