If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Pie Charting with Missing and Changing Data
Hi All I am trying to create a dynamic pie chart (has to be a pie chart) that can automatically take into account the fact that the number of series to be graphed each time changes, and that there may be missing data within the list of data The 'Plot Empty Cell' section under Tools - Options does not seem to apply to Pie Charts as I cannot select any of the options here I have tried using the formula =OFFSET('Sheet1'!$B$7,1,0,COUNTA('Sheet1'!$B:$B)-1,1) in the named range for the data called Spend, and =OFFSET(Spend,0,-1) in the corresponding data labels, but I this still graphs areas where there are missing cells Does anyone have any suggestions how to achieve this Thanks Karen -- ksp ------------------------------------------------------------------------ ksp's Profile: http://www.excelforum.com/member.php...fo&userid=6267 View this thread: http://www.excelforum.com/showthread...hreadid=546340 |
#2
|
|||
|
|||
Pie Charting with Missing and Changing Data
Hi,
Here is an example that does not plot missing data. http://www.andypope.info/charts/piezeros.htm Cheers Andy ksp wrote: Hi All I am trying to create a dynamic pie chart (has to be a pie chart) that can automatically take into account the fact that the number of series to be graphed each time changes, and that there may be missing data within the list of data The 'Plot Empty Cell' section under Tools - Options does not seem to apply to Pie Charts as I cannot select any of the options here I have tried using the formula =OFFSET('Sheet1'!$B$7,1,0,COUNTA('Sheet1'!$B:$B)-1,1) in the named range for the data called Spend, and =OFFSET(Spend,0,-1) in the corresponding data labels, but I this still graphs areas where there are missing cells Does anyone have any suggestions how to achieve this Thanks Karen -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#3
|
|||
|
|||
Pie Charting with Missing and Changing Data
Hi Andy Thanks for that - It seems to work however I have to manually delete any data labels for that line that has missing data Do you know of a way to get the data labels to also update with the changing data? The link that you gave me says that it updates the legand but not for me. Thanks Karen -- ksp ------------------------------------------------------------------------ ksp's Profile: http://www.excelforum.com/member.php...fo&userid=6267 View this thread: http://www.excelforum.com/showthread...hreadid=546340 |
#4
|
|||
|
|||
Pie Charting with Missing and Changing Data
I just downloaded and test the workbook and it functions for me.
Zero items are removed from pie, data labels and legend. To get rid of zero data labels use a custom number format (whole values only) 0;;; Cheers Andy ksp wrote: Hi Andy Thanks for that - It seems to work however I have to manually delete any data labels for that line that has missing data Do you know of a way to get the data labels to also update with the changing data? The link that you gave me says that it updates the legand but not for me. Thanks Karen -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#5
|
|||
|
|||
Pie Charting with Missing and Changing Data
I have to confess I was guilty of not reading the instructions properly - something I give my users a hard time for Thanks for your help this is going to save one of my users days worth of work Ta KP -- ksp ------------------------------------------------------------------------ ksp's Profile: http://www.excelforum.com/member.php...fo&userid=6267 View this thread: http://www.excelforum.com/showthread...hreadid=546340 |
#6
|
|||
|
|||
Pie Charting with Missing and Changing Data
No problem glad it helped and thanks for your honesty
Cheers Andy ksp wrote: I have to confess I was guilty of not reading the instructions properly - something I give my users a hard time for Thanks for your help this is going to save one of my users days worth of work Ta KP -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Data Trucated in word merge with excel source | BAW | Mailmerge | 6 | May 3rd, 2006 04:10 PM |
Concerned about different Access versions--need serious help! | NightowlMania | General Discussion | 22 | January 16th, 2006 01:34 PM |
COMBINING 2 DATA TABLES FROM 2 DIFFERENT SOURCES | Coastal Studies | New Users | 11 | January 2nd, 2006 03:46 PM |
Help again from Ken Snell (Query) | Randy | Running & Setting Up Queries | 22 | August 29th, 2005 08:15 PM |
HELP - surface plots with missing data (How?) | Takeshi | Charts and Charting | 3 | March 11th, 2004 02:13 PM |