Chart
Posted by rosie on February 28, 2001 2:07 PM
I am trying to delete the contents of cells to keep a chart line from going to zero if a cell is emptied by a formula.
=IF(B2="",IF(B2="",D2,""),IF(SUM(B2:B3)=B2,B2,""))
I tried the clear contents code I found posted, but maybe I did it wrong.
Private Sub CommandButton1_Click()
Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("D1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("C2").Select
If Range("C2") = "<1" Then Range("C2").ClearContents
:: End Sub
Posted by David Hawley on February 28, 2001 3:10 PM
Hi Rosie
If you go to my web site and look at the links "Chart Tips and Tricks" and "Dynamic Named Ranges" you my see some ideas that you could adopt.
Dave
OzGrid Business Applications
Posted by Mark W. on February 28, 2001 3:52 PM
Rosie, if you ask me you're making this far more
difficult that need be. Use #N/A rather than "".
Look what happens when you attempt to chart
=SERIES("Y",,{1,2,"",4},1). It changes to
=SERIES("Y",,{1,2,0,4},1). That's your problem.
Now try =SERIES("Y",,{1,2,#N/A,4},1). Look! No
problem. #N/A in a chart series allows allow you
to interpolate a line across data gaps.
Posted by David Hawley on February 28, 2001 6:10 PM
Mark, I hope you didn't pinch idea from my web page :o)
As Mark has said (and my web page) Excel will not plot #N/A. Or =NA(). It also will not plot hidden Rows/Columns.
Dave
OzGrid Business Applications
Posted by Rosie on March 01, 2001 4:54 AM
Mark, Dave:
Thank you both. Problem solved.
