Combine 2 named-ranges for a single series line chart

**ElmerS** · 03-22-2010, 05:19 PM

Hi,

In the attached WB -

Sheet A has a 3 cells (A1:A3) named range: AA
Sheet B has a 7 cells (A11:A17) named range: BB

In sheet C I would like to plot a 'Line' chart from a Single Series - the combination of AA+BB

What I have in mind is that the single series source should be:
2, 3, 1, 7, 5, 9, 8, 4, 6, 10

In the series source I've tried to type:

=Book18.xls!AA, Book18.xls!BB
or:
='Book18.xls'!AA, 'Book18.xls'!BB

Excel excepts only: =Book18.xls!AA OR =Book18.xls!BB but no both

Any assistance will be appreciated,

Elm

**teylyn** · 03-22-2010, 08:21 PM

Elmer, you can use range names and you can combine several non-contiguous ranges into one data series. In both cases, though, the data must be on the same sheet.

In a workbook with three defined ranges, "first", "second" on Sheet1, "third" on Sheet2, the following formula can combine the non-contiguous ranges of the same sheet

=SERIES(,(Sheet1!$A$1:$A$3,Sheet1!$A$7:$A$9),(Book4!first,Book4!second),1)

The combined series formula omits the first argument, so the default series name is used. The second argument specifies the category labels of two non contiguous ranges. These could also be range names. The third argument specifies two ranges as data values.

All ranges are on the same sheet.

Range "third" is on Sheet2. As soon as you replace range "second" with "third" in above formula, it errors.

hth

**ElmerS** · 03-23-2010, 03:23 AM

Thank you.

In other words, if I understood you correctly - my request, as posted, cannot be accomplished.

Elm

**ElmerS** · 03-23-2010, 03:50 AM

teylyn,

Will you be kind to take a look at the following thread.

http://www.microsoft.com/office/comm...ee3&sloc=en-us

Did I misunderstood something ?

Although the OP didn't attach a WB his question was very clear, so was the answer which got a "green V" which means it helped the OP.

What am I missing ?

Thanks, Elm

**teylyn** · 03-23-2010, 07:13 AM

Dunno. My experience shows that it only works if the ranges referenced in the Series formula are on the same sheet. As soon as you reference ranges on another sheet, it bombs.

I also tried to create a range name to combine the two ranges, but with no success.

Maybe the OP in the linked question had a typo in the sheet name and their ranges were all on the same sheet. ?? Since the thread in your link is relatively recent, maybe you could enquire there?

**ElmerS** · 03-23-2010, 07:17 AM

Thanks,

As I count on your experience - the 'typo' option sounds more than logic.

Elm

**Frank02** · 12-02-2014, 07:12 PM

aqui tienes un ejemplo
here is an example

**Frank02** · 12-02-2014, 07:18 PM

sorry , I can't upload file

**FDibbins** · 12-02-2014, 07:21 PM

Frank, welcome to the forum

1st, this thread is over 4 years old, I doubt anyone is still following it
2nd, I dont see who asked for a file from you?
3rd, and more important, Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.