How to change chart data source when Formula object is too short?

I know that:

I can assign data source

1) Series.Values=Range(...) - suitable
2) Chart.SetSourceData souce:=range(...)... - suitable
3) Series.Formula/FormulaLocal="" - not suitable because string is too short

I can read data source

1) x = Series.Formula/FormulaLocal - not suitable because string is too short

The question is:

How to read data source not using Formula object?

What does this mean?

"x = Series.Formula/FormulaLocal - not suitable because string is too short"

Jon Peltier, Microsoft Excel MVP
http://PeltierTech.com

When I want to change data source for Excel chart I need three things:

1) Find out/Read current data source.

2) Change current data source.

3) Set/Write changed data source formula.

So,

"x = Series.Formula/FormulaLocal - not suitable because string(data type of variable) is too short"

mean that I could not read whole long formula for the chart. If formula will be longer than 255 characters, it will be unpossible to get correct data source and consequntaly change it!

Are these explanations enough to realize the problem?

Could you post the series formula, so I know exactly what you mean? If you've encountered this limit, I want to make sure my solution for this works in your case as well as in the general case.

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

This is a sample Series formula - The formula in my model is different but the length and idea are the same (Idea: I mean - using formula with unmerged Ranges)

=Series("Super demo series";(Demo1!$D$30;Demo1!$F$30;Demo1!$H$30;Demo1!$J$30;Demo1!$L$30;Demo1!$N$30;Demo1!$P$30;Demo1!$R$30;Demo1!$T$30;Demo1!$V$30;Demo1!$X$30;Demo1!$Z$30);(Demo1!$D$31;Demo1!$F$31;Demo1!$H$31;Demo1!$J$31;Demo1!$L$31;Demo1!$N$31;Demo1!$Q$31;Demo1!$S$31;Demo1!$U$31;Demo1!$W$31;Demo1!$Y$31;Demo1!$AA$31;Demo1!$AC$31);1)

You can say that I can rebuild my model to merge the Ranges, but it is not convenient for the users of the model - growth rate is realy useful indicator in the model.

Here is the sample structure of the model:

	A	B	C	D	E	F	G
1		Absolute	Growth rate	Absolute	Growth rate	Absolute	Growth rate
2		End of 2005	For 2005	End of 2006	For 2006	End of 2007	For 2007
3	Sales	$100	-	$105	5,0%	$210	100,0%

Formula will be =Series(A$3$;(B$2$;D$2$;F$2$;.....);(B$3$;D$3$;F$3$;.....);1)

Thank very much!

I can tell you one thing. If you give five minutes thought to the arrangement of data, you can save yourself hours of frustration. Especially with charting, but also in so many other ways.

Why not arrange the data like this:

                 2005   2006   2007
Absolute Sales
Sales Growth

Another thing you could do is have the real data off screen (in a range away from the visible part of the screen, or on a separate sheet), arranged for useful charting, then use formulas in the display region to show the annual figures and growth rates. It's often too hard to compromise with a single worksheet range to make it good for (a) on screen viewing, (b) printed output, (c) charted output, (d) data source for pivot tables or other calculations. Use one data range for each type of data you need, and link it together in a clever way so you only ever have to update one set of values.

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

Answer to your question: "Why not arrange the data like this?"

- I thought that it will not be so fine looking and convenient, but found myself mistaken! -)

Thank you for your good critical analysis!

One problem is that I have couple of models formated in old way and now I need time to change them, change chart data links manually. My purpose was to eliminate routine tasks concerned with copying sheets containing charts and relinking charts to new sheets using VBA!

It would be realy good to have ability to get access to whole chart data source formula in the future!!!

What can you say about this? Will new version of Excel allow me and many other interested people to do such beautiful things!!!?

Thank you again!

What can you say about this?

What I will say is with Excel, if you get the data right in the first place, you will save hours of aggravation. Charts were designed to accept a row or column of X values, and a row or column of Y values. Having alternating X and Y values in the same row is making it difficult. Putting the X and Y into distinct rows and columns adjacent to each other makes it work better, and often as you discovered, look better.

If you need the data table to look one way and the chart requires data the other way, use two (or more) different data ranges. Worksheets are cheap, hard drives are enormous and dirt cheap, your time is expensive and your frustration doubly so. Put the original data into a sheet somewhere, maybe even out of sight of the user. Set up a visual data range using Copy and Paste Special - Link, so it's arranged the way you like it, and linked to the original. Set up a chart source data range, again using Paste Special - Link, so it's arranged the way the chart likes it. Five extra minutes of work to set up the different data ranges, five hours less work to set up for printing or for making a chart.

You can eliminate routine tasks more easily by understanding what the program expects than wishing it expects something else. When you understand it, it even makes sense, and you will be more likely to set things up efficiently in the future.

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

Jon,

I have what is probably a very basic question which is similar to that covered in this post.

I've been putting together automated Excel spreadsheet macros for quite a while ... but I'm no expert. One thing I haven't been able to figure out is how to have a chart automatically adjust itself to varying size data sets returned from database queries. This is causing me particular problems when the X axis values are a list of dates. As long as all the cells contain a valid date all is OK, but once blank cells are included at the end of the data, nothing behaves as it should.

Is it necessary to determine the number of rows returned and use this to construct the chart data range expression? I had hoped there would be some means of indicating to the chart that it should automatically adjust to suit the data set returned.

Can you point me in the right direction? I'm sure this must be able to be done, but haven't found anything obvious in Excel help or on the net.

Regards,

Greg Shearer

Greg -

You need to set up dynamic ranges, which in turn are used as the chart series' data source:

http://peltiertech.com/Excel/Charts/Dynamics.html

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

Jon,

Thanks so much! I don't know how long it would have taken me to discover this technique.

Thanks again!!

Greg Shearer