Chart only data with values in Table (ignore blanks)

[Akshay]

Hi all,

I need help with setting up a template to create a control chart. I only want to show values that are in the table, ignoring blanks/NA's but want it dynamic so the formulas are pasted down to around 300 rows (or more if necessary), and the user simply pastes in their data and the chart populates automatically as the source data will be set to look at these 300 rows.

I've got most of it working but there is still empty space to the right of the chart.

What am I doing wrong or is there a way around this?

Thanks in advance.

[Pete_UK]

It is difficult to envisage what you are describing, so it would help if you attached a sample Excel workbook, and use that to explain what you are trying to achieve, maybe with a Before and After sheet.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Pete

[Akshay]

Thanks Pete,

I've now attached the workbook. The "before" tab shows the chart with the range selected to row 30. The "after" tab shows how I'd like it to look.

I want to share the template with my colleagues so all they need to do is paste in their own data and the chart just picks up the rows with data in, ignoring the blanks. This way, it leaves it simple for them to use.

[MrShorty]

It sounds like it should be a standard "dynamic chart using dynamic named ranges" type of problem: http://peltiertech.com/dynamic-charts/

[Akshay]

I looked at that but couldn't get my head around the forumula's and they seem to indicate I can't have any blanks in the data. I want the chart to look at a large number of rows but only want those values pasted in by the user to be plotted on the chart, so that only those x-axis variables are shown and no white space to the right (due to the empty cells beneath the data.

This is so the user simply pastes in their data and the chart picks it up automatically, ignoring the blank rows underneath, which I want to keep to allow for larger data sets to be pasted without the need for the user to copy down the formula's that create the control limits.

[Akshay]

I looked at that but couldn't get my head around the forumula's and they seem to indicate I can't have any blanks in the data. I want the chart to look at a large number of rows but only want those values pasted in by the user to be plotted on the chart, so that only those x-axis variables are shown and no white space to the right (due to the empty cells beneath the data.

This is so the user simply pastes in their data and the chart picks it up automatically, ignoring the blank rows underneath, which I want to keep to allow for larger data sets to be pasted without the need for the user to copy down the formula's that create the control limits.

[Pete_UK]

It's probably best to extract only the valid data to another sheet and use that as the source for your graph. I'm going out soon, but I'll take a look later on.

I wasn't sure why you had all those #N/A errors in the file that you attached - is that just to produce blanks on the right side of your graph?

Pete

[Akshay]

Yes and no. I had those NA's to try and eliminate the blanks on the right but it didn't work. I was just trying to set up an easy to use template where the chart looks at a set number of rows eg. 30 so up to 2.5 years of data by month.

The user can then paste their own data in, which could range from 12 to 30 months and the chart is automatically populated but with no blanks on the chart if their data consisted of less than 30 months.

[MrShorty]

I looked at that but couldn't get my head around the forumula's and they seem to indicate I can't have any blanks in the data. I want the chart to look at a large number of rows but only want those values pasted in by the user to be plotted on the chart, so that only those x-axis variables are shown and no white space to the right (due to the empty cells beneath the data.

I would be curious what you tried that failed. It might help us see exactly what you are having trouble understanding.

With your sample data, I would have expected a name definition similar to =OFFSET(Before!$B$2,1,0,COUNTA(Before!$B$3:$B$3000),1) for the X_axis_data (similar functions for the remaining data series). Did you try something like that, or something else.

The point about "blanks" in the data depends on exactly what you have and what you are doing, and how those things affect the 4th argument of the OFFSET() function. I chose the COUNTA() function for this because your example shows the x-axis data containing a block of text strings followed by a lot of truly blank cells (no "empty string as the result of a formula" cells). Other scenarios will require different functions for this argument to accurately determine how far down the data extend.

I created two named ranges and a dynamic chart based on those ranges as an example.

[Akshay]

Hi MrShorty, thanks for the attached worksheet you provided.

I have extended the chart range now to capture the other columns of data for the control limits but if I add more x and y data then the chart won't display these unless I increase the chart data range when clicking on "Select Data" on the chart. Is there a way around this? I would prefer the user not to do this themselves as the whole exercise is geared around providing a fully automated solution.