Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 21st 14, 08:29 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2012
Posts: 5
Default One extra x axis item that wont go away - can anyone help?

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 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 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 item on the x axis.

Can anyone help?

Thanks and regards, Mark








  #2   Report Post  
Old April 21st 14, 08:39 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,872
Default One extra x axis item that wont go away - can anyone help?

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #3   Report Post  
Old April 21st 14, 08:44 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2012
Posts: 5
Default One extra x axis item that wont go away - can anyone help?

On Monday, 21 April 2014 15:29:40 UTC+8, Mark Stephens wrote:
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 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 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 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
  #4   Report Post  
Old April 21st 14, 08:47 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2012
Posts: 5
Default One extra x axis item that wont go away - can anyone help? -CORRECTED WITH THANKS BY CLAUS IS A NANOSECOND!

On Monday, 21 April 2014 15:29:40 UTC+8, Mark Stephens wrote:
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 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 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 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
extra info is showing up in my secondary x axis, I can't get rid o Meenie Charts and Charting in Excel 1 March 2nd 10 05:30 PM
Chart adds in extra date values on the X axis Pete Rooney Charts and Charting in Excel 6 May 1st 08 11:01 AM
Date format from excel to CVS file wont. Change in CVS wont stay. Fish''s Mermaid Excel Worksheet Functions 1 October 14th 06 12:28 AM
I AM TRYING TO ADD INFO TO THE CATAGORY AXIS BUT IT WONT LET ME C. CINDY Charts and Charting in Excel 2 March 23rd 05 08:18 PM
ActiveX / C++ - Extra menu item Pixy Excel Programming 0 July 7th 04 02:51 PM


All times are GMT +1. The time now is 01:31 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017