Named-range source-data for pie charts on copied worksheets

[ashley73]

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, are:
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.

 

[Andy Pope]

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,COUNT(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

 

[ashley73]

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

Thanks for your response, I'll keep trying, I hope someone comes up
with something though. Has the need to use the piezeros workaround
been alleviated in versions post Excel 2000?

 

[Andy Pope]

If you want to do this without code use auto filter.
Select the chart and check tools > Options > Chart > 'Plot visible cells
only' is set.
The when you filter out zero values the chart will update accordingly.

No version current deal with this directly. And I doubt they will.

Cheers
Andy

 

[ashley73]

I may have figured out a somewhat inelegant yet arguably effective
solution. The piezeros workaround is brilliant, albeit way above my
level of comprehension. Unfortunately it appears that it doesn't work
for copied sheets. One alternative workaround, which so far looks
satisfactory, is to do away with the piezeros complexity, and use the
original table data as source data. Values and percentages can be
hidden using custom formats, leaving just the labels. Assuming the
zero value records are at the bottom of the source table (my data will
always be in order of decreasing value), they get piled up at the top
of the chart. They can be hidden by the strategic placing of a small
rectangle, no border, white fill, placed at 12 o'clock. As I said,
inelegant and basic, yet it looks like it does the job.

The only problem could be that it may partially obscure the last non-
zero label, but it seems ok unless the value is ~1% (unlikely in my
situation). When there is a small value, say 2%, the zero labels get
pushed to the side a tad. Another small rectangle can be strategically
placed to cover that area also. Obviously this solution has no effect
on the legend (whereas piezeros does), so you would have to choose
between showing all labels in the legend or not showing the legend at
all.

Granted that my solution is based on a number of assumptions, but it
works for me, and may be helpful to someone else.