One extra x axis item that wont go away - can anyone help?

[Mark Stephens]

Hi there,

I have set up a dynamic chart using named ranges. Here's a description of how it is set up, hopefully when you have seen it in its totality someone will be able to help me get rid of the extra item, I have tried everything I can to no avail.

The source sheet for the chart is on a sheet named Source3 which consists of column A from A1 to A40 numbered 1 to 40. Column 2 contains values populated from and array which clears the existing values before being deposited in column B from B1 to B however many values there are (that's the dynamic bit).

The chart is a simple bar chart in another sheet in the same workbook namedCHARTS


I have two named ranges: First Cht3_SRC which is referenced:

=OFFSET(Source3!$B$1,0,0,COUNTA(Source3!$B:$B,1))

And Chart3Labels referenced:

=OFFSET(Source3!ChartValues,0,-1)

My chart works beautifully except that the x axis has one blank item more than it should have (i.e. if the series in Source3 column B has 5 items it shows a chart correct in every respect except that it has a sixth blank itemon the x axis.

Can anyone help?

Thanks and regards, Mark

 

[Claus Busch]

Hi Mark,

Am Mon, 21 Apr 2014 00:29:40 -0700 (PDT) schrieb Mark Stephens:

I have two named ranges: First Cht3_SRC which is referenced:

=OFFSET(Source3!$B$1,0,0,COUNTA(Source3!$B:$B,1))
try:
=OFFSET(Source3!$B$1,,,COUNTA(Source3!$B:$B))

And Chart3Labels referenced:

=OFFSET(Source3!ChartValues,0,-1)

try:
=OFFSET(Cht3_SRC,,-1)


Regards
Claus B.

 

[Mark Stephens]

Hi there,



I have set up a dynamic chart using named ranges. Here's a description ofhow it is set up, hopefully when you have seen it in its totality someone will be able to help me get rid of the extra item, I have tried everything I can to no avail.



The source sheet for the chart is on a sheet named Source3 which consistsof column A from A1 to A40 numbered 1 to 40. Column 2 contains values populated from and array which clears the existing values before being deposited in column B from B1 to B however many values there are (that's the dynamic bit).



The chart is a simple bar chart in another sheet in the same workbook named CHARTS





I have two named ranges: First Cht3_SRC which is referenced:



=OFFSET(Source3!$B$1,0,0,COUNTA(Source3!$B:$B,1))



And Chart3Labels referenced:



=OFFSET(Source3!ChartValues,0,-1)



My chart works beautifully except that the x axis has one blank item morethan it should have (i.e. if the series in Source3 column B has 5 items itshows a chart correct in every respect except that it has a sixth blank item on the x axis.



Can anyone help?



Thanks and regards, Mark

Hi Claus,

Thanks for your speedy reply, unfortunately didn't correct it, kind regards, Mark

 

[Mark Stephens]

Hi there,



I have set up a dynamic chart using named ranges. Here's a description ofhow it is set up, hopefully when you have seen it in its totality someone will be able to help me get rid of the extra item, I have tried everything I can to no avail.



The source sheet for the chart is on a sheet named Source3 which consistsof column A from A1 to A40 numbered 1 to 40. Column 2 contains values populated from and array which clears the existing values before being deposited in column B from B1 to B however many values there are (that's the dynamic bit).



The chart is a simple bar chart in another sheet in the same workbook named CHARTS





I have two named ranges: First Cht3_SRC which is referenced:



=OFFSET(Source3!$B$1,0,0,COUNTA(Source3!$B:$B,1))



And Chart3Labels referenced:



=OFFSET(Source3!ChartValues,0,-1)



My chart works beautifully except that the x axis has one blank item morethan it should have (i.e. if the series in Source3 column B has 5 items itshows a chart correct in every respect except that it has a sixth blank item on the x axis.



Can anyone help?



Thanks and regards, Mark

Hi CLaus, I stand corrected I didn't copy your solution correctly first time, when I did it corrected it thank you so so much, really appreciate your help, kind regards, Mark