# combining ranges

#### kloy

##### New Member
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?

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
For joining ranges in code see the 'Union Method'.
I prefer to have my range in a worksheet and change/link cells as necessary.

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.

What are the references of your 2 ranges? Will these ranges change dynamically, or will they always be 5 and 5?

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.

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 )

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?

"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.

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!

Aha, there you go! I knew that would do it.

Replies
14
Views
765
Replies
3
Views
455
Replies
4
Views
531
Replies
8
Views
265
Replies
0
Views
105

1,203,663
Messages
6,056,623
Members
444,878
Latest member
SoupLaura

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back