not showing blank cells in a chart
 

I would like to have a pie chart that selects data points from a static range
only if they are non-blank cells. searching on the internet i found the code
that i am useing now which gets me a pie chart with only the datapoints i
want, but the legend still shows all possible points even if they are blank.
i have defined two names as ranges:

statevalues =OFFSET(statechtdata!$A$2,0,0,COUNTA(statechtdata! $A:$A)-1,1)

statevaluesY =OFFSET(statevalues,0,1)

and the chart series is as follows:

=SERIES(statechtdata!$A$1,'sample portfolio.xls'!statevalues,'sample
portfolio.xls'!statevaluesY,1)

also, the pie chart originally only had one color for all the data point
which i went through and began to manually change but I am wondering if there
is a setting or an option to enusre that they come in as different colors in
the first place.
--
Thanks,

Galen

not showing blank cells in a chart
 

AFAIK, with a single series Pie chart there is no automatic way to prevent
blank points showing in the Legend.

You can programmatically delete individual LegendEntries if say the relative
point is blank. To add them back if values change, need to delete and
recreate the Legend. Post back if not sure how to go about that.

To reset the default individual segment colours, select the pie, and format
Fill with Automatic.

Regards,
Peter T


"Galen" wrote in message
...
I would like to have a pie chart that selects data points from a static
range
only if they are non-blank cells. searching on the internet i found the
code
that i am useing now which gets me a pie chart with only the datapoints i
want, but the legend still shows all possible points even if they are
blank.
i have defined two names as ranges:

statevalues =OFFSET(statechtdata!$A$2,0,0,COUNTA(statechtdata! $A:$A)-1,1)

statevaluesY =OFFSET(statevalues,0,1)

and the chart series is as follows:

=SERIES(statechtdata!$A$1,'sample portfolio.xls'!statevalues,'sample
portfolio.xls'!statevaluesY,1)

also, the pie chart originally only had one color for all the data point
which i went through and began to manually change but I am wondering if
there
is a setting or an option to enusre that they come in as different colors
in
the first place.
--
Thanks,

Galen

not showing blank cells in a chart
 

yeah that's what i ended up doing. Thanks.
--
Thanks,

Galen


"Peter T" wrote:

AFAIK, with a single series Pie chart there is no automatic way to prevent
blank points showing in the Legend.

You can programmatically delete individual LegendEntries if say the relative
point is blank. To add them back if values change, need to delete and
recreate the Legend. Post back if not sure how to go about that.

To reset the default individual segment colours, select the pie, and format
Fill with Automatic.

Regards,
Peter T


"Galen" wrote in message
...
I would like to have a pie chart that selects data points from a static
range
only if they are non-blank cells. searching on the internet i found the
code
that i am useing now which gets me a pie chart with only the datapoints i
want, but the legend still shows all possible points even if they are
blank.
i have defined two names as ranges:

statevalues =OFFSET(statechtdata!$A$2,0,0,COUNTA(statechtdata! $A:$A)-1,1)

statevaluesY =OFFSET(statevalues,0,1)

and the chart series is as follows:

=SERIES(statechtdata!$A$1,'sample portfolio.xls'!statevalues,'sample
portfolio.xls'!statevaluesY,1)

also, the pie chart originally only had one color for all the data point
which i went through and began to manually change but I am wondering if
there
is a setting or an option to enusre that they come in as different colors
in
the first place.
--
Thanks,

Galen