Series formula is too long? 1

[Cheech]

Hey there I keep getting this message when I try to update the source data for a chart in MS Excel? I realise that it is telling me that there are 2 many characters in the formula but all we have been doing is right click the graph going to source data and control clicking the new cell to add to the chart.
Any one got an easy solution for this? Please. I dont want to go to Chelsea!!!

 

[Dreamboat]

New cell? Generally, a new series is added, not just one cell. What does the source data say before, and then what does it say after.
techsupportgirl@home.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word

 

[Cheech]

I probably havent explained myself very well.

We have a spreadsheet that gets updated with a new figure each day(it actually logs Intranet visits) we then take an average on a weekly basis. It this figure that is used to generate a chart. When we have the figures on a monday we just used to go into the spreadsheet and rightclick the chart and select Source data then just control click the new cell. Before and after source data is

before:
=Sheet1!$E$4,Sheet1!$E$11,Sheet1!$E$18,Sheet1!$E$25,Sheet1!$E$32,Sheet1!$E$39,Sheet1!$E$46,Sheet1!$E$53,Sheet1!$E$60,Sheet1!$E$67,Sheet1!$E$74,Sheet1!$E$81,Sheet1!$E$88,Sheet1!$E$95,Sheet1!$E$102,Sheet1!$E$109,Sheet1!$E$116

after:
=Sheet1!$E$4,Sheet1!$E$11,Sheet1!$E$18,Sheet1!$E$25,Sheet1!$E$32,Sheet1!$E$39,Sheet1!$E$46,Sheet1!$E$53,Sheet1!$E$60,Sheet1!$E$67,Sheet1!$E$74,Sheet1!$E$81,Sheet1!$E$88,Sheet1!$E$95,Sheet1!$E$102,Sheet1!$E$109,Sheet1!$E$116,Sheet1!$E$123

I hope that makes more sense to you than to me I dont want to go to Chelsea!!!

 

[JVFriederick]

Try changing the Sheet1 name to A.

 

[Dreamboat]

I agree with JV. Those formulas are a bit much. Also, you should usually just be able to copy a range (okay, in your case, a cell), click on the chart and hit paste.
techsupportgirl@home.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word

 

[Cheech]

Yeh I guess the formula was long, I ended up putting the weekly figures in a column of their own and just taking in the whole column as in

=Sheet1!R1C6:R137C6

Not what I wnated because now the graph only displays a marker for each entry and does not produce a line graph so we live with it.. Live long and make your kids suffer..

 

[Cheech]

Sussed the lines with

Tools>Options>Chart tab & using the Interpolated option

Just thought some of you might like to know. Live long and make your kids suffer..

 

[DaleWatson123321]

Cheech,

I noticed, based on your following comment, that your problem is still not really resolved.

"Not what I wanted because now the graph only displays a marker for each entry and does not produce a line graph so we live with it.. "

Proposed Solution...

Have you considered setting up a relatively simple piece of code which would, at the "click of a button", EXTRACT the required data to a separate range (or separate sheet) where the data is then contiguous, and the code would automatically include all the values in this contiguous range.

If you still want to resolve the problem of being to produce a "line graph", please advise. Or, perhaps you might want to email me a "sample" of your file (include dummy data if necessary), and I'll modify it with the necessary code.

Incidentally, JV's suggestion of using a short name in place of "Sheet1" is a good means of reducing the size of a formula. Another method is to use short Range Names for each of the cells (e.g. "_1", "_2", "_3", etc.). By using range names, ONLY the range names appear in the formula, so it could be made VERY short.

However, I expect the BEST solution my first one - i.e. to have a button which, when clicked, EXTRACTS the data and updates the graph.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca