getting chart legend to ignore #NA s!
 

Hi there,
I have a pei chart based on a dynamic table that sometimes contains #NA for
the rows data.

The chart updates fine, but in the legend i get an entry for every row -
including all the NAs! is there any way i can avoud this that you can think
of?

I've used VBA plenty in Access, so am prepared to have a bash writing any
code in excel with any pointers you may have!
thx

getting chart legend to ignore #NA s!
 

I would use IF IS ERROR in each cell or ISNA to get a blank cell instead of a
#N/A

"arran1180" wrote:

Hi there,
I have a pei chart based on a dynamic table that sometimes contains #NA for
the rows data.

The chart updates fine, but in the legend i get an entry for every row -
including all the NAs! is there any way i can avoud this that you can think
of?

I've used VBA plenty in Access, so am prepared to have a bash writing any
code in excel with any pointers you may have!
thx

getting chart legend to ignore #NA s!
 

To follow up on EJ's response, if you post the formula(s) which return #NA!
errors, someone here can tell you how to trap the error (i.e., have 0 or ""
returned in place of the error).

Dave
--
Brevity is the soul of wit.


"EJ" wrote:

I would use IF IS ERROR in each cell or ISNA to get a blank cell instead of a
#N/A

"arran1180" wrote:

Hi there,
I have a pei chart based on a dynamic table that sometimes contains #NA for
the rows data.

The chart updates fine, but in the legend i get an entry for every row -
including all the NAs! is there any way i can avoud this that you can think
of?

I've used VBA plenty in Access, so am prepared to have a bash writing any
code in excel with any pointers you may have!
thx

getting chart legend to ignore #NA s!
 

Hi,

This example shows how to exclude zeros.
http://www.andypope.info/charts/piezeros.htm

You should be able to adapt for non numeric by replacing the formula in
C1 with,
=IF(ISNUMBER(B2),B2+(ROWS(B2:B$2)/1000*(B2<0)),0)

and copying down.

Another alternative is to hide rows and set chart option 'plot visible
cells only'. Then you only need code to hide rows.

Cheers
Andy

arran1180 wrote:
Hi there,
I have a pei chart based on a dynamic table that sometimes contains #NA for
the rows data.

The chart updates fine, but in the legend i get an entry for every row -
including all the NAs! is there any way i can avoud this that you can think
of?

I've used VBA plenty in Access, so am prepared to have a bash writing any
code in excel with any pointers you may have!
thx