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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
For joining ranges in code see the 'Union Method'.
I prefer to have my range in a worksheet and change/link cells as necessary.
 
Upvote 0
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.
 
Upvote 0
What are the references of your 2 ranges? Will these ranges change dynamically, or will they always be 5 and 5?
 
Upvote 0
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.
 
Upvote 0
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 )
 
Upvote 0
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?
 
Upvote 0
"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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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
Back
Top