Circumvent "Series formula is too long"

[Morten]

Hi,

I have a weekly weekly data set that is the basis for a trend graph for one
year. This works ok until about the 16th week, then I get "Series formula is
too long".

It seems the use of named range is the only way around this...?

I have tried this, looked through previous posts and replies here, but this
does not work for me. It seems only applicable to very simple data sets and
graphs.

The data set is as follows:

There are two rows of headers (X) - the first is the week's number, the row
below has the value of the week - for instance "Incoming". Each week contains
4 columns, so there is no continous range over the weeks for "Incoming".

The Y contains the company names.

I don't know how the formatting will be for this post, but I'll try and make
a simple table-example of this:

W49 W49
W50
Incoming Outgoing
Incoming
Company1 200 185 105
Company2 3500 3320 2705


Regards
Morten

 

[Jon Peltier]

The best thing to do is to rearrange your data. Five minutes with your
data will save five hours of aggravation later.

If you can't rearrange the existing data, set up another sheet or part
of a sheet that has contiguous ranges. Link back to the discontiguous
ranges in the original data. Use this new range for the chart's source data.

- Jon

 

[Morten]

Hi,

The data is allready rearranged to the maximum possible, and that's was no 5
minute job!

What I have found is the problem is when two named ranges are used (it works
with just one).

Named range one contains week 32-42 - let's call it "w32_42".
Named range two contains week 43-53 - let's call it "w43_53".

When this is entered in graph's range:
"='Sheet'!companies;'Sheet1'!w32_42;'Sheet1'!w43_53",

no errors are reported, but all the weeks of the two named ranges combined
are listed twice in the graph, instead of just once.

Morten