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

Please Login or Register  to view this content.

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 MSP77079

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.

They are objects. The chart resides right next to the data table for both charts in this specific incident

Here is adaptation of Bryan's code to fit your situation.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

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.

Originally Posted by MSP77079

Please Login or Register  to view this content.

Forgive me for my ignorance, but where do I need to put these lines of code?

Let's go back to your original idea of linking the chart title to a cell in the workbook.

Just as an example, assume that:
"614 Machine Uptime Percentage" is in cell H1
"%Month Year%" is in cell H2
"MTD PCT (%c81%)" is in cell H3

Somewhere (for example, say it is cell K1), you put this formula:
=H1&CHAR(10)&H2&CHAR(10)&H3

Now, click the title of the chart, then in the formula bar type:
=K1

In your original message, you said

%name% is the automatically populated item.

Where does it (they) come from? What cells are they found in? What do they look like? When they are automatically populated, does the routine that does teh automatic population delete the cells before repopulating them? Or merely "write over" what was there before? Do my questions make any sense?

If you are able to upload a copy of your workbook, it would be much easier to answer (and to know that the answer I give is really going to work for you).