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?
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

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,689
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,689
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,689
"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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,546
Messages
5,838,011
Members
430,526
Latest member
NiceGuyWithExcel2007

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top