Ignore Plotting Zeros & Empty Cells In Chart

I am having a problem getting Excel to not plot zero values or empty cell values in a line chart.

The details:

I am attempting to offer spreadsheet users a way to pick which data is charted in a simple line chart. On the worksheet their is a drop box that allows a user to choose which column they want charted (the choices is "=Admin_Custom_Field_Names" which are 4 heading names a user picks). So, the user picks one of the heading names from a drop down validation list and then the chart updates. Here is what I have behind the scenes.

The chart data is a column with this formula:

=IF(Custom_Chart_Variable=Custom1_Header,'2009 Log'!AD3,IF(Custom_Chart_Variable=Custom2_Header,'2009 Log'!AE3,IF(Custom_Chart_Variable=Custom3_Header,'2009 Log'!AF3,IF(Custom_Chart_Variable=Custom4_Header,'2009 Log'!AG3,""))))

Basically, that formula checks to see if the user picked column 1,2,3 or 4 to be charted and uses the coorosponding data to chart.

Now, the problem is that even if the corosponding column has no data entered then the formula produces a "0" and the chart plots the point 0. So I fixed that and had it enter a "" if the column data was empty but the chart stills an empty cell as 0. Then, I tested removing the formula above from a few cells and noticed that the chart does not plot those cells. So, eventhough the cell has no value (or a "" value) Excel DOES chart it if there is a formula in it. If the cell is truely empty then it does not chart it. I am failing to find a way in Excel 2007 to ignore plotting zeros or cells that equal "". How can this be accomplished? The other twist is that I desire this to work in Excel 2003 and 2007 for PC and Excel 2004 for mac.

Many thanks,

Guy

Re: Ignore Plotting Zeros Or Empty Cells As Zero In Chart

Quote from AAE

Try using =NA() instead of =""
If having #N/A in the cell is objectionable, use conditional formatting to hide it.

You could also hide the columns or rows that are empty.

Many thanks for your reply. I can't hide the rows that are empty because the chart is only one part of the sheet and it would effect too much. But, I like the #N/A solution. Here is what works, so far (with little check so far)

=IF(Custom_Chart_Variable=Custom1_Header,IF('2009 Log'!AD348="",NA(),'2009 Log'!AD348),IF(Custom_Chart_Variable=Custom2_Header,IF('2009 Log'!AE348="",NA(),'2009 Log'!AE348),IF(Custom_Chart_Variable=Custom3_Header,IF('2009 Log'!AF348="",NA(),'2009 Log'!AF348),IF(Custom_Chart_Variable=Custom4_Header,IF('2009 Log'!AG348="",NA(),'2009 Log'!AG348),""))))

Seems funny Excel doesn't have an ignore zeros solution built in. I thought previous version of Excel had this. I might be wrong, though.

Guy