Dynamic Series Range

[Guest]

I am currently producing a dynamic chart that is populated by a potentially
different sized amount of data.

I have been using Peltier's technicque to create dynamic ranges for the X
values and Y values, and this works fine to a certain extent. However, I want
the X values to incorporate two columns and not the one. For example:

Col M Col N Col O
X Values X Values#2 Y Values
Gender Male 1
Female 2
D-O-B January 1
March 2
April 3
September 4

....etc etc

The named range for the X Values is identified by the following calculation:

=OFFSET('Workbook'!$N$108,0,0,COUNTA('Workbook'!$N$108:$N$200))

Is there a way of slightly amending this so as Column M is also included as
part of the X Range?

Many Thanks

 

[Andy Pope]

Hi,

Change the start offset to -1
Increase the number of columns to 2. Currently it is not specified and is
therefore defaulted to 1.

=OFFSET('Workbook'!$N$108,0,-1,COUNTA('Workbook'!$N$108:$N$200),2)

Cheers
Andy

 

[Guest]

Thanks Andy, that works a treat.

Phil

Hi,

Change the start offset to -1
Increase the number of columns to 2. Currently it is not specified and is
therefore defaulted to 1.

=OFFSET('Workbook'!$N$108,0,-1,COUNTA('Workbook'!$N$108:$N$200),2)

Cheers
Andy