Dynamic Chart Ranges

ScottyWm

Board Regular
I've searched long and hard for answers, without any luck.

I've got data in worksheet 'Summary-All'!A56:A100 that I want to use for the chart series names. I made this a Named range called Sup_Name =OFFSET('Summary-All'!\$A\$56,0,0,COUNTA('Summary-All'!\$A:\$A),1).

I've got values in C56:N100 that I want to use for the series values. I made this a Named range called PPM =OFFSET('Summary-All'!\$C\$56,0,0,COUNT('Summary-All'!\$C:\$N),12)

I'm trying to make a chart that will update with new series as rows are added or deleted from this range. Since I've got these multiple series, I am trying to put the named ranges into the "Data Range" tab of the Source Data in the chart, not the "Series" tab. This might be problem 1, I don't know because I can't seem to get past problem 2 which is that since my series are in rows and not columns it tells me that the maximum # of data series in a chart is 255, and forces the series into columns. And it is deleting my range names and putting in cell reference for the range!

I thought the whole point of using the named ranges is so that it will grab the rows with data, and ignore those without. But it seems to be grabbing the whole column starting with the offset cell. When I do a =Sum(PPM) or =Count(Sup-Names) in a worksheet cell, it calculates correctly.

I think I need to tweak my formula in the range names, but don't know. Help me please, it sounds so easy I must be doing something dumb!

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Howdy. It seems as if you named range is working on the rows, rather than the columns. Instead of this,

Sup_Name =OFFSET('Summary-All'!\$A\$56,0,0,COUNTA('Summary-All'!\$A:\$A),1)

try this:

Sup_Name =OFFSET('Summary-All'!\$A\$56,0,0,1,COUNTA('Summary-All'!\$56:\$56))

As a hint, it is usually better to have the data placed vertically rather than horizontally.

Okay... that did change the named range selection. Now it doesn't go all the way to the bottom of the sheet. But for some reason now it stops the selection after 13 rows? It doesen't select all the way to the end of the data rows.

Hmmm....

ScottyWm said:
Okay... that did change the named range selection. Now it doesn't go all the way to the bottom of the sheet. But for some reason now it stops the selection after 13 rows? It doesen't select all the way to the end of the data rows.

Hmmm....

What is the formula that you are now using?

First, you need to take a little time and figure out how the OFFSET function works. Simply duplicating what you found elsewhere and expecting (hoping?) it will work with your specific circumstances is a sure path to frustration. Check what help contains about the different arguments and how they affect the range that results from the function.

Second, it is not possible to adjust the number of series in a chart with a named formula. Named formulas make it possible to adjust the number of data points in a series. To understand why, see how one uses a named formula in a chart. It is only in the context of a specific series.
Names in Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/names_in_charts.html

ScottyWm said:
I've searched long and hard for answers, without any luck.

I've got data in worksheet 'Summary-All'!A56:A100 that I want to use for the chart series names. I made this a Named range called Sup_Name =OFFSET('Summary-All'!\$A\$56,0,0,COUNTA('Summary-All'!\$A:\$A),1).

I've got values in C56:N100 that I want to use for the series values. I made this a Named range called PPM =OFFSET('Summary-All'!\$C\$56,0,0,COUNT('Summary-All'!\$C:\$N),12)

I'm trying to make a chart that will update with new series as rows are added or deleted from this range. Since I've got these multiple series, I am trying to put the named ranges into the "Data Range" tab of the Source Data in the chart, not the "Series" tab. This might be problem 1, I don't know because I can't seem to get past problem 2 which is that since my series are in rows and not columns it tells me that the maximum # of data series in a chart is 255, and forces the series into columns. And it is deleting my range names and putting in cell reference for the range!

I thought the whole point of using the named ranges is so that it will grab the rows with data, and ignore those without. But it seems to be grabbing the whole column starting with the offset cell. When I do a =Sum(PPM) or =Count(Sup-Names) in a worksheet cell, it calculates correctly.

I think I need to tweak my formula in the range names, but don't know. Help me please, it sounds so easy I must be doing something dumb!

Thanks for the suggestions... Have checked all the help on these functions in-depth for hours on end it seems.

One thing I know for sure is that the help I get from this board is first rate and usually much easier to understand than the microsoft help!

Guess I should have closed this post long ago with "got it fixed" cause I did with the help from y'all!

Replies
2
Views
785
Replies
3
Views
428
Replies
7
Views
368
Replies
1
Views
360
Replies
1
Views
598

1,203,045
Messages
6,053,187
Members
444,644
Latest member
keepontruckinc4

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back