Charts & Data Labels Using =NA()

[CWatts]

Short version: is there a way to show data labels for everything with data, but not label =NA() values?

Long version & background:
There is a small table based on VLOOKUPs which populate the data for the chart. The data is currency ranging from -$10 to $30. These are vlookups coupled with IFERROR statements that display an NA() if there is no data. The NA is crucial as text is charted at 0 and I need the chart to not display anything for weeks without data.

Now the issue that comes up is that I want the #N/A's to be hidden or displayed as "" in the data labels, but i'm stuck.

The only semi related post I could find is as follows, but it doesn't work with Excel 2007:

http://www.mrexcel.com/forum/showthread.php?t=77006&highlight=#N/A+Data+label

My data label number is currently formatted as such:

$0.00;[Red]-$0.00;$0;""

 

[msaadkamal]

Thank You man.. i really appreciate it..

 

[Dokat]

Hi - When i used custom formatting 0;;; on a bar chart with similar #NA issues it did not get rid of NA's. I wonder what i am doing wrong or is there a way around it?

Thanks

 

[Jon Peltier]

Dokat -

Go back and read the second post in this thread. NA() isn't for bar charts. Use a zero instead.

 

[Dokat]

Hi- I didn't use any formula. I have a dynamic chart and when ever a button is uncheck data labels show#na. I was wondering how can I get rid of na data labels

 

[Jon Peltier]

You uncheck a box and #N/A appears in a cell somewhere. This is because of a formula of some sort. Track down the cell and you'll find the formula.