Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Named-range source-data for pie charts on copied worksheets
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. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Named-range source-data for pie charts on copied worksheets
On Mar 18, 4:06 am, Andy Pope wrote:
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? |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Named-range source-data for pie charts on copied worksheets
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 wrote: On Mar 18, 4:06 am, Andy Pope wrote: 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? |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Named-range source-data for pie charts on copied worksheets
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting - 2 Worksheets one Named Range | Excel Worksheet Functions | |||
same named range on multiple worksheets? | Excel Discussion (Misc queries) | |||
Setting source data range with Charts | Charts and Charting in Excel | |||
How can i set the source-data-range of pivottable2 to the source . | Excel Discussion (Misc queries) | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |