MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Plotting zero values on a chart


Posted by Duane Kennerson on November 17, 2000 8:11 PM

I have a chart in my workbook that plots values based on a link on the worksheet.
If there is no value in the link, the chart will put a dot on 0 on the X axis.
I used the solution on putting "NA()" instead of "" in an IF clause to get rid
of the dot on 0. Now the problem is how do I get rid of the #N/A that is displayed
instead of a number. Is there some way I can change the font color in the IF formula
when it displays "#N/A" and then change the font color back if it is a normal number???
I tried to do it using VBA on worksheet activate but I kept getting a type mismatch
error when I activated the sheet. Is this confusing? I hope not. Can anyone help???
Thanks in advance.


Posted by marbel on November 21, 2000 9:33 AM

Check out the included link. Basically, you want to conditionally format your cells with the "formula is" =ISERROR(A1), and the format selection of white text on a white background.
Good luck,
mb


Posted by Duane Kennerson on November 21, 2000 3:45 PM

Its almost perfect. What happens is I press a button that activates
a macro that will refresh the page so that new numbers will be plotted
on the graph. The conditional format works perfect except for when I
press the button to update the graph, it will not put the most recent
information in the cells that generate the graph information. The graph
updates correctly but the cell that is supplying the information
is blank. I could put in information for 3 days of numbers and then
press the refresh button and the first two days will show up but the
third won't. I hope this makes sense because I'm confusing myself. Do
you know what is going on???
Thanks again....
Duane


Posted by Duane Kennerson on November 21, 2000 4:25 PM