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