Linking and Ignoring in Excel Graph

This is probably a very simple issue, but I don't know how to word the search.

I have two issues with an excel graph to automate it.

My first problem is that I have to manually select the source data when the data changes inside my spreadsheet. I have 35 slots that represent a 5 week period. Because the first day of the month does not always start on the first day of the week, I have to allow the extra 5 slots in my template. I have the cells set to show blank when there is no data entered in certain cells. Therefore, only days that the machine is up gets put into the 35 cell table. When I do this, the blank cells show up as 0 on the graph and it skews the line. I need some solution that will allow me to tell the graph to ignore zeros.

The second issue is the title in my graph. I have a set title, but need it to pull "Month Year" and "c81" from the worksheet it is on. I cannot seem to get it to populate automatically in the label. I would like it to look like the following:

614 Machine Uptime Percentage
%Month Year%
MTD PCT (%c81%)

where the %name% is the automatically populated item. I don't necessarily have a problem with hiding a cell and linking the label to that cell inside the graph, but I need it to change those two items automatically.

Im certain that a resolution to both of these items exist and I am certain it is an easy thing that I will be kicking myself in the rear over once I find out the resolution, but I need to get this done and im tired of wasting time. I hope this makes sense. If you need me to post an excel file as an example, I will be more than happy to. Your help will be greatly appreciated.

Originally Posted by Bryan Hessey

Hi,

for the zero values, with the chart active, select Tools, Options, Chart, and Plot zero as Interpolated, if that alone fails then the cells that are #N/A are ignored.

Unsure about adjusting the title, I think that might be a piece of VB code, still to come.

hth
---
added, something along the lines of

  ActiveChart.ChartTitle.Select
    Selection.Characters.Text = "=test test title test" & Chr(10) & "" & Chr(10) & "Month 18"

Is there only one chart on your worksheet?
---

There are two charts on the page

Originally Posted by Bryan Hessey

are we referring to the first or second of the two charts on the page?

Sorry for the delay, I will be applying the solution to both charts on the page.

Is it a separate chart sheet or are these "chart objects" (charts that are displayed on a worksheet that has rows and columns)?

The code will be slightly different from one to the other. The code Bryan posted is for the former case ,,, a chart sheet.

Originally Posted by Bryan Hessey

Hi,

for the zero values, with the chart active, select Tools, Options, Chart, and Plot zero as Interpolated, if that alone fails then the cells that are #N/A are ignored.

---

I set the chart options to Interpolated, but it did not make any changes. I suspect the reason is that the cells are not blank, they just appear to be so the sheet does not show any errors. Here is the exact equation I have one of the fields:

=IF(C4="","",week1!B2)

It appears that since the cell is not actually empty, it is showing on the chart as Zero. I deleted the statements from the cells and the zeros went away on the chart. Do you know of any way to make it ignore a null return and not just an empty cell?

Originally Posted by Bryan Hessey

Hi,

trying to not clash with MSP77079 (who is doing an excellant job), the formula

=IF(C4="",#N/A,week1!B2)

will allow the blanks, and a little Conditional formatting (#N/A then white font on white background) will hide the #N/a from view.

hth
---

There are two issues with this resolution. When you set the chart not to show 0s, it looks at the cells to see if they are blank. It will still show zeros as long as there is anything written in the cell. The If statement itself is what is creating the Zeros. I even went as far as referencing a blank cell just to test and even though it returned null, the statement was still in the cell and it showed as Zero. The other problem is that there will be data in each one of these cells at some point. If I change the font to white, it will not show up when there is true data in there.