combining ranges

kloy

New Member
Joined
Aug 15, 2005
Messages
24
i would like to add one line to a linechart consiting of several sets of data.

the first 5 points are part of data set A; the second 5 points are part of data set B.

i am quite familiar with lists and the offset command. my favorite solution is to greate a new list bij joining two other lists.

somthing like =offset() & offset()

any ideas? tips?
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
For joining ranges in code see the 'Union Method'.
I prefer to have my range in a worksheet and change/link cells as necessary.
 

kloy

New Member
Joined
Aug 15, 2005
Messages
24
up to now my workbook is quite clean. no code, no unnecesary cells. i would like to keep it like this

an other solution would be to get control over where the line begins, in the chart.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
What are the references of your 2 ranges? Will these ranges change dynamically, or will they always be 5 and 5?
 

kloy

New Member
Joined
Aug 15, 2005
Messages
24

ADVERTISEMENT

they will change dynamicaly.

up to the actual date i want to plot sales values. from now on until the end of the year i would like to plot the forecast.

i want both of them in one line.
but they are stored in seperate rows.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
Do you have a month pointer that could be tested to see when the figures from one row should end and the figures from the other row sould start?

What are the references of your 2 ranges? ( again )
 

kloy

New Member
Joined
Aug 15, 2005
Messages
24

ADVERTISEMENT

i do have a month/date pointer that could be checked. the 2 ranges should change when the values in one range end.

i do not exactly know what you mean by "references of my 2 ranges"?

do have a solution in mind?
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
"references of your 2 ranges" means : What are the range references that would define what each range is? You know $B$14:$M$14 and $B$23:$M$23 for example!

Yes, I have a solution in mind, and without knowing how your pointer works or having range references, it's a bit difficult to show you. The method involves having a defined name, containing an IF statement testing the pointer, and switching between 2 ranges in an array formula kind of way.
 

kloy

New Member
Joined
Aug 15, 2005
Messages
24
got it!

something like:

Code:
{=IF(C8:M8<>"",C8:M8,C7:M7)}

it is working and is what i wanted.

i didn't realize that array-formulas return arrays...

thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,228
Messages
5,571,000
Members
412,353
Latest member
SofiaV
Top