I am attempting to generate dynamic column charts based on grouped data in a
separate sheet. The user selects the data, and after putting the sheet
together, the chart needs to graph This Year and Last Year as separate
series, but use only Country, Region, and Unit, no sub-Unit
I am looping through the rows, and putting together the string to assign to
..Values for each series, but with a change to the structure of the data, now
I am getting a much too long string to assign to .Values (255 char. limit)

The basic string for the first series that I get from my loop is 308
characters.
strSeries1 =
"=Summary!$E$14,Summary!$E$15,Summary!$E$16,Summar y!$E$23,Summary!$E$24,Summary!$E$27,Summary!$E$31, Summary!$E$35,Summary!$E$38,Summary!$E$39,Summary! $E$45,Summary!$E$46,Summary!$E$48,Summary!$E$52,Su mmary!$E$55,Summary!$E$56
Summary!$E$58,Summary!$E$59,Summary!$E$61,Summary! $E$62,Summary!$E$64,Summary!$E$65"

I have tried assigning the first cell to the .Values property and using
..Extend, but that has not worked for me either. I have checked out every
conceivable site that i have found, and yet nothing has worked. Please
Help!!!

The data on the Summary sheet is basically formatted as follows:
A E F
Last Year This Year
Country
Region
Unit
Sub-Unit
Sub-Unit
Unit
Sub-Unit
Region
Unit
Sub-Unit
Region
Unit
Sub-Unit
Sub-Unit

Since it's not really a dynamic solution anyway, why not copy the cells to
another range, without the gaps. Then you're back to the ideal, charting
contiguous regions.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"sqlfan13" <nospam.dustinmueller_13.at.hotmail.com wrote in message
...
I am attempting to generate dynamic column charts based on grouped data in
a
separate sheet. The user selects the data, and after putting the sheet
together, the chart needs to graph This Year and Last Year as separate
series, but use only Country, Region, and Unit, no sub-Unit
I am looping through the rows, and putting together the string to assign
to
.Values for each series, but with a change to the structure of the data,
now
I am getting a much too long string to assign to .Values (255 char. limit)

The basic string for the first series that I get from my loop is 308
characters.
strSeries1 =
"=Summary!$E$14,Summary!$E$15,Summary!$E$16,Summar y!$E$23,Summary!$E$24,Summary!$E$27,Summary!$E$31, Summary!$E$35,Summary!$E$38,Summary!$E$39,Summary! $E$45,Summary!$E$46,Summary!$E$48,Summary!$E$52,Su mmary!$E$55,Summary!$E$56
Summary!$E$58,Summary!$E$59,Summary!$E$61,Summary! $E$62,Summary!$E$64,Summary!$E$65"

I have tried assigning the first cell to the .Values property and using
.Extend, but that has not worked for me either. I have checked out every
conceivable site that i have found, and yet nothing has worked. Please
Help!!!

The data on the Summary sheet is basically formatted as follows:
A E F
Last Year This Year
Country
Region
Unit
Sub-Unit
Sub-Unit
Unit
Sub-Unit
Region
Unit
Sub-Unit
Region
Unit
Sub-Unit
Sub-Unit

Thanks Jon!

While it didn't seem as simple as it was, your answer made me realize that
was exactly what needed to be done.


"Jon Peltier" wrote:

Since it's not really a dynamic solution anyway, why not copy the cells to
another range, without the gaps. Then you're back to the ideal, charting
contiguous regions.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"sqlfan13" <nospam.dustinmueller_13.at.hotmail.com wrote in message
...
I am attempting to generate dynamic column charts based on grouped data in
a
separate sheet. The user selects the data, and after putting the sheet
together, the chart needs to graph This Year and Last Year as separate
series, but use only Country, Region, and Unit, no sub-Unit
I am looping through the rows, and putting together the string to assign
to
.Values for each series, but with a change to the structure of the data,
now
I am getting a much too long string to assign to .Values (255 char. limit)

The basic string for the first series that I get from my loop is 308
characters.
strSeries1 =
"=Summary!$E$14,Summary!$E$15,Summary!$E$16,Summar y!$E$23,Summary!$E$24,Summary!$E$27,Summary!$E$31, Summary!$E$35,Summary!$E$38,Summary!$E$39,Summary! $E$45,Summary!$E$46,Summary!$E$48,Summary!$E$52,Su mmary!$E$55,Summary!$E$56
Summary!$E$58,Summary!$E$59,Summary!$E$61,Summary! $E$62,Summary!$E$64,Summary!$E$65"

I have tried assigning the first cell to the .Values property and using
.Extend, but that has not worked for me either. I have checked out every
conceivable site that i have found, and yet nothing has worked. Please
Help!!!

The data on the Summary sheet is basically formatted as follows:
A E F
Last Year This Year
Country
Region
Unit
Sub-Unit
Sub-Unit
Unit
Sub-Unit
Region
Unit
Sub-Unit
Region
Unit
Sub-Unit
Sub-Unit