Excel 2007 : Dynamic Chart Data Range

Hi
I hoping someone can help me please.

I have a spreadsheet that contains:
column AA has a ranking integer from 1 to 100,
column AB has a list of percentages,
columns AC to AL hold up to one percentage each dependent on whether the top row is populated with a name (if it's not populated there is no value below) brought through from Sheet 2.

I've produced a Column chart that shows the value for each name against the entire population (each series is a different name).

To define the Chart Range I've defined a NAME as =OFFSET('Sheet1'!$AA$1,0,0,COUNTA('Sheet1'!$A$2:$A$101),(COUNTA(Sheet2!$C$2:$C$11)+2))
where Sheet 2 contains the list of names that I wish to display.

When I enter the NAME into Chart Data Range it correctly identifies how many columns of data to use and produces the graph.

However, if I thin add another Name in the graph doesn't change as the Data Range has changed to a spreadsheet reference.

How can I stop this happening and the graph automatically identify how many data cloumns to use?

Hopefully you understand my query!

Thanks

Much appreciated!
Thanks