In chapter 7 of John Walkenbach's 'Excel Charts' book from 2003, he has a section on creating a dynamic chart where the series plotted depends on the location of the Active Cell in the worksheet.
In the new edition of this book, 'Excel 2007 Charts', his Table of Contents is very similar, but...