Dynamic Chart Ranges

[ScottyWm]

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!

 

[shades]

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.

 

[ScottyWm]

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....

 

[shades]

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?

 

[tusharm]

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

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!

 

[ScottyWm]

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!