Results 1 to 2 of 2
2011-05-29, 23:47 #1
- Join Date
- May 2002
- Thanked 0 Times in 0 Posts
Series Reference If Null In Chart
In my worksheet (Sheet2) contains dates and numbers in columns B and C, starting in row 2through 50 that are linked to data from Shee1.
Cells B2:B50 have dates that are linked toSheet1.
Cells C2:C50 have numbers that are linked toSheet1.
In both of these columns (B2:B50 and C2:C50), I have an IF statements if the corresponding linked cell in Sheet1 is blank, to insert a space. IF(ISBLANK(Sheet!B2), “ “,(Sheet!B2)
On Sheet3, I chart/graph the data from Sheet2 (Number and Date). The chart works perfect except when there is a blank cells from Sheet2, the graph line will go to zero.
I’m looking for a formula/method so that when the SERIES reference is null, the graph line will not display in the chart.
I know I could remove the formulas from Sheet2 that do not have a value from the linked cells and then each week copy/input the formula for that week, however, someone else will be inputting the data and I will lock all cells in both Sheet1and Sheet2 with the exceptions of Column C of Sheet1 for the user to input that week's data.
I’ve attached a “simplified” example of the workbook where I’ve removed all data that’snot related to my question. On my attached workbook, if you will look at Sheet2/cell C44, there is no value so the graph on Sheet3 falls to zero. I would like the graph line to stop at the last value (C43 = 201).
I would appreciate any suggestions or recommendations.
Last edited by sbdale; 2011-05-29 at 23:53.
2011-05-30, 05:55 #2
- Join Date
- Jul 2002
- Pittsburgh, Pennsylvania, USA
- Thanked 342 Times in 335 Posts
Instead of something like:
replace the null string with an NA:
NA errors do not plot. Text and all other errors plot as zero.