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

Discussion in 'Excel Charting' started by Mark Stephens, Apr 21, 2014.

  1. 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
     
    Mark Stephens, Apr 21, 2014
    #1
    1. Advertisements

  2. Mark Stephens

    Claus Busch Guest

    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
     
    Claus Busch, Apr 21, 2014
    #2
    1. Advertisements

  3. 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 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, Apr 21, 2014
    #3
  4. Re: 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 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
     
    Mark Stephens, Apr 21, 2014
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. vp23larry
    Replies:
    2
    Views:
    277
    Collins32
    Jun 23, 2005
  2. Mike
    Replies:
    1
    Views:
    129
    Andy Pope
    Oct 19, 2005
  3. Aaron
    Replies:
    1
    Views:
    225
    Del Cotter
    Aug 17, 2007
  4. AnnieW
    Replies:
    4
    Views:
    164
    Del Cotter
    Aug 15, 2008
  5. wmaker51
    Replies:
    0
    Views:
    122
    wmaker51
    May 14, 2009
  6. Meenie
    Replies:
    1
    Views:
    68
    Jon Peltier
    Mar 2, 2010
  7. Joe M.
    Replies:
    1
    Views:
    134
    Andy Pope
    May 5, 2010
  8. darrel
    Replies:
    1
    Views:
    283
    Tobi Ackerman
    Jan 22, 2011
Loading...