Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamic chart (2002)

    I have a list of figures in column A which populate a chart. I want to modify the SERIES formula so that when an additional item is added Excel will automatically add a new data point (column) to the chart.
    I believe this can be done using COUNT, OFFSET functions - but can't quite remember the formula? Thanks, Andy.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Dynamic chart (2002)

    You need to define a name (let's call it <code>ChartData</code>) as something like:
    <code>=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A) ,1)</code>
    and then use that as the source data (enter <code>=Sheet1!ChartData</code> for the Values)
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic chart (2002)

    You the man. Thank you!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic chart (2002)

    1) Create a dynamic named range.

    Select Insert | Name | Define...
    Enter a name in the 'Names in workbook' box, e.g. MyValues.
    Enter a formula like this in the 'Refers to' box:
    <code>
    =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
    </code>
    Substitute the correct sheet name, and adjust the range if necessary - the above assumes that the data begin in A2, with a header above them in A1.
    Click OK (or click Add, then OK).

    You now have defined a dynamic named range. It will adjust itself automatically as data are added or removed.

    2) Click on the chart.
    Select Chart | Source Data.
    Activate the Series tab.
    Change the Values specification to
    <code>
    ='Sheet1'!MyValues
    </code>
    substituting the name of the sheet and of the named range.
    Click OK.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •