Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Seattle, Washington
    Posts
    251
    Thanks
    29
    Thanked 2 Times in 2 Posts

    Range names and charts (Excel 2003)

    <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> I have some simple data, consisting of a Date column, a data column, and an average column. I
    Bob Wall

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Range names and charts (Excel 2003)

    If you create the chart ranges from a range of cells, and add new data to the end, you will have to adjust the chart range when you append data.

    If you INSERT within the data range, the chart will automatically be updated. I frequently will have a blank row past my data range (I color it so I see it), and then when I add data, I just insert rows to expand the range.

    Another method is to use dynamic range names using offset to automatically change the range names to account for the data.
    John Walkenbach discusses this technique.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Seattle, Washington
    Posts
    251
    Thanks
    29
    Thanked 2 Times in 2 Posts

    Re: Range names and charts (Excel 2003)

    <img src=/S/weep.gif border=0 alt=weep width=21 height=16> Using the blank line is more work than changing the data daily.

    I think my main problem is tied to the fact that I can't make the range name stay in the source data for the chart. The source data becomes the coordinates of the cells, rows and columns. Even after changing the size of the range name, I still have to go back and change the source data, by either changing the cell address, or typing in the range name again.
    Bob Wall

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range names and charts (Excel 2003)

    Have a look at my Autochrt.zip available from my website below. You have to include the rangename into the series formulas of each of the chart's series. The download I gave shows you how.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Range names and charts (Excel 2003)

    I am afraid I do not understand what you are saying.

    Did you setup like John Walkenbach's site details?

    Do you have any macros that change the source data? Open the workbook with disabled macros and see if they change.

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Seattle, Washington
    Posts
    251
    Thanks
    29
    Thanked 2 Times in 2 Posts

    Re: Range names and charts (Excel 2003)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> I'm attaching a simple worksheet with charts to this message. My original worksheet has several months of data. I can't use the Range Names after I change the data for a new day. I can go back and do a lot of work to reassign the Range Name, and then enter that Range Name into the source data box. Or, I can just change the digits in the source data box to reflect the new cell address. I have several charts I have to update with the new data, and it becomes error prone, boring, and tiresome to do it that way.

    No, I didn't do what he called for on his site.
    Bob Wall

  7. #7
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range names and charts (Excel 2003)

    Bob,

    The attachment did not make it.....

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  8. #8
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Seattle, Washington
    Posts
    251
    Thanks
    29
    Thanked 2 Times in 2 Posts

    Re: Range names and charts (Excel 2003)

    Okay. I'll try it again.
    Bob Wall

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Range names and charts (Excel 2003)

    Look at the technique in John Walkenbachs site. I think this is exactly what you are after.

    Create an Xrange, a YDataRange, and a YRunningAvg range. You can have the lengths of all the ranges be dependent on the number of points in the Data column.

    Once you add the "dynamic ranges" to the chart, they will expand as new data is added automatically.

    Steve

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range names and charts (Excel 2003)

    The point is DON'T enter the range names in the chart's sourcedata box. Take a close look at the links already provided in this thread to study how this can be done.

    The trick is to put the names in the proper places in the formula that shows in the formula bar when you select a series of the chart. The exact syntax can be derived from the sources already provided.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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