Hi,

Not sure you are going to be able to do this without code.

I put your worksheet name formula in A1 and modified the formula to use
it and INDIRECT.

=OFFSET(INDIRECT(DataInColumn!$A$1&"!$E$2"),0,0,CO UNT(INDIRECT(DataInColumn!$A$1&"!$E$2:$E$8")),1)

But when you copy the sheet although the named ranges travel and update
themselves the links in the chart are broken.
The series formula becomes,
=SERIES(,{"A","B","C","D","E","F","G"},{1,2,3,1,5, 6,7},1)

Cheers
Andy

wrote:
I have to finally concede that I'm stuck and have to ask for advice. I
am using Excel 2000 and have been able to use Andy Pope's help on
"Automatic removal of zero values in pie chart". That works fine. But
if I copy the worksheet, the pie chart on the copied (new) worksheet
references the named range as defined on the original worksheet.

My named ranges, which the pie chart references, a
PieLabels=OFFSET(blank!$O$26,0,0,COUNT(blank!$N$26 :$N$34),1)
PieValues=OFFSET(blank!$N$26,0,0,COUNT(blank!$N$26 :$N$34),1)

So, one solution might be to define the same named range on different
worksheets and prefix the Range Name by the worksheet name. One
problem with this though is that it appears to only work for existing
worksheets, I need something that will work for new (copied)
worksheets. Reason being is that the first worksheet will be blank,
and I wish to make a copy of this each time I have a new set of data.
But when I do this, the source data for the pie chart on the new
worksheet is derived from the named range as defined on the worksheet
which was copied.

So I have figured out that a cell formula for the current worksheet
is: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)

But I don't know how that helps or what I can do with that formula in
relation to named ranges and the source data for my pie chart. So,
what am I missing? How can I make my named ranges and pie charts that
little bit more flexible and dynamic?

Note that I do not wish to use a macro or VBA programming to achieve
this.