Using Dynamic Ranges for Excel Charts

[sriaknt1983]

Hi,

I am want to create a dynamic chart which will get ignore the #NA values in the table and it should not plot them.
Refer to the attached excel file for more details.
I want a graph which will only displays the values for "Mars, Jupiter, Venus, Pluto" and ignore the #NA values.

Thanks

[delforum]

All you have to do is adjust your data series from =Sheet1!$B$2:$AX$10 to =Sheet1!$B$2:$AX$6 (this can be done when you click on the plotted trend lines at the top on the ribbon under Select Data)

This will avoid those rows.

If you have more data, you can recopy your information like I did in sheet2 and filter them out with the autofilter in the top row.

See attached sheet.

[sriaknt1983]

Thanks but this might not work for me as I am looking for an automatic process.Is there a way in which the graph can automatically pick data excluding all #N/A values below "Pluto"? I believe using "Named Ranges" will solve the problem but I am not able to make it work.

[delforum]

This is the formula, that I use all the time

=OFFSET(CLIENT_Summary!$A$1,0,0,COUNTA(CLIENT_Summary!$A:$A),COUNTA(CLIENT_Summary!$A:$A))

I will adapt it to your usage following this post. You enter this formula in the Names manager, under the formulas tab on the ribbon. Basically you make a range by using an offset. You start at the first cell, then, from there add 0 rows and 0 columns, and adjust from the start the number of cells that are not blank (in your case the number of cells that are not NA), and adjust the vertical by the same.

[delforum]

I am getting very close with creating a name

ChartNoNA
=OFFSET($B$2,0,0,COUNTIF($B:$B,"<>"&NA())+1,COUNTA($2:$2)+1)

Just have to tweak the plus ones a bit, or start the range at another spot.

[delforum]

=OFFSET(Sheet1!$B$3,-1,0,COUNTIF(Sheet1!$B:$B,"<>"&NA())+1,COUNTA(Sheet1!$2:$2)+1)

Worked for me....named it ChartNoNA, see posted workbook.

[sriaknt1983]

Thanks!!!
Is there a way that it can be tweaked so that any legitimate (non #N/A)values that are added after Pluto are also picked by the graph?
Like there is another row titled "Earth" which has some non-zero values.

[sriaknt1983]

Hi,
If I delete the data series say "Pluto" the graph doesnot exclude it but shows a series "#N/A". Also if I add another series after Pluto it doesnot get picked up by the chart.
It will be great if you can help me resolve these problems.
I am just curious if the graph use the named ranges that you have created? I am asking because I coudn't see a reference.

=OFFSET(Sheet1!$B$3,-1,0,COUNTIF(Sheet1!$B:$B,"<>"&NA())+1,COUNTA(Sheet1!$2:$2)+1)

Worked for me....named it ChartNoNA, see posted workbook.

[sriaknt1983]

Hi,
I modified the definition of the Named Range "ChartNONA" to OFFSET(Sheet1!$B$3,-1,0,Sheet1!$B$20,COUNTA(Sheet1!$2:$2)+1)

$B$20- capture the number of cell which are non blanks AND non #N/A.

So the named series is able to capture my requirement i.e when i add another series it includes that in the range and similarly when I removed a series with #N/A values it excludes that from the range.

Now I would like to use this named range in a graph. I provide Chart Data Range = 'Test.xls'!chartNONA
The graph pciks the required range and display it properly as per my requirement.

The problem is when I modify the range and provide new data series the chart doesnot pick that and if i click on select data and check the chart range it has become something similar to "'Sheet1'!$B$2:$AX$8"

The input that we have provided is gone

Anyway I can do a work around and fix this problem??

Thanks

[teylyn]

Hello,

with dynamic charts, you can define each chart series to include only valid data cells and ignore cells for the series that show #N/A.

In this situation, you are not trying to ring-fence the extent of the series, but you want a dynamic solution whether or not to include a row as a data series in the first place.

This is not how it works.

For each chart, you will need to define the number of series included in the chart. You cannot omit/include a series with dynamic ranges.

What's the problem, anyway? Include the whole data table in the range and just delete the empty series from the legend.

[sriaknt1983]

Can empty or #N/A series be deleted from the legend without using any VBA code?

Include the whole data table in the range and just delete the empty series from the legend.

[teylyn]

Just click the label in the legend and hit delete.

[sriaknt1983]

I know..I was hoping for a more automatic process.

[delforum]

Perfectly dynamic formula, you can add rows of planets, or dates of data. I used your formula modifications from test, and made them universal.

ChartNoNA range variable in the Names Manager

=OFFSET(Sheet1!$B$3,-1,0,COUNTA(Sheet1!$B:$B)-COUNTIF(Sheet1!$B:$B,"<>"&NA())+1,COUNTA(Sheet1!$2:$2)+1)

Just keep your blank spot at B3, and keep the start of your data where it is at, with the N/A's all at the end of the list


See the attached file.

[delforum]

I see your issue. When you add lines, and want to update the chart, you have to go back and put in the new range, by simply typing in ChartNoNA. The only way around this is running a macro. I created one by using record.

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.PlotArea.Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(3).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SetSourceData Source:=Range("ChartNoNA")
   ' ActiveChart.SetSourceData Source:=Range("B2:AX11")
End Sub

See the attached sheet, I made it run when you change something on the worksheet.

When you insert rows, it will give you a small error about #REF, but when you hit OK, the chart is updated.

You must enable macros security settings.

[sriaknt1983]

Hi,

I am sorry for bumping this old thread. I was trying to do this without using Macro and looks like there is a way to do this by using the SERIES formula and named ranges for "Chart Data Range".
I have created 3 named ranges

ChartNoNA: OFFSET(Sheet1!$B$3,-1,0,$b$20,COUNTA(Sheet1!$2:$2)+1) ---Capture the complete data which dont have a blank and #N/A time series.

ChartNoNA_Series: OFFSET(Sheet1!$B$3,-1,0,Sheet1!$B$20,COUNTA(Sheet1!$2:$2)+1) --- Captures only the series name (Mars, Jupiter, Venus, Pluto, etc.)

ChartNoNA_Values: OFFSET(Sheet1!$B$3,0,1,Sheet1!$B$19,48) ---- Captures the values for the abouve time series.

Now I am not sure how excatly I can use the above named ranges(along with others if required) to create a dynamic graph.

Any help on this will be greately appreciated !!

Thanks.