Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatic update for charts and statistics (Excel 2002 SP 2)

    I know this topic has been addressed before, but I can't find it without the search so it's probably been a while. <img src=/S/beep.gif border=0 alt=beep width=15 height=15>

    I have to add a row of data every week. I have a chart and some statistics that are based on the data. How do I specify the ranges so that things update without have to change all of the formulas every week.

    Thanks for your help.

  2. #2
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic update for charts and statistics (Excel 2002 SP 2)

    Lets say that we currently have data in A1:B40 in Sheet1. A1:B1 houses appropriate labels. The data type of B2:B40 is numeric. We can define a name for this range by means of a dynamic formula. The result is often called a named dynamic range. The definition is as follows...

    Activate Insert|Name|Define.
    Enter Drange (from data range) as name in the Names in Wokbook box.
    Enter the following in the Refers to box:

    =Sheet1!$A$1:INDEX(Sheet1!$B:$B,MATCH(9.9999999999 9999E+307,Sheet1!$B:$[img]/forums/images/smilies/cool.gif[/img])

    Click OK.

    When you add a new record to A:B, Drange will include that immediately.
    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic update for charts and statistics (Excel 2002 SP 2)

    Thank you. The range names work great. I still have one question.

    In the Match formula, why do you use 9.99999999999999E+307? What's it doing?

  4. #4
    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: Automatic update for charts and statistics (Excel 2002 SP 2)

    It is the largest value possible in excel. It keeps going through the list looking for a number >= to this. When it finally gets to the end of the list, it just accepts that.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic update for charts and statistics (Excel 2002 SP 2)

    I've been using this dynamic formulas a lot lately, but now I'd like to add a lot of names with a dynamic formula. It is very cumbersome to add names like this. Does anyone have an quick and easy trick to accomplish this?

  6. #6
    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: Automatic update for charts and statistics (Excel 2002 SP 2)

    Some thoughts:
    You can EDIT the refers to a similar one an give it a new name

    You could create a table of the names, and reference columns and create a macro to read the table and create the names. based on the slelected region of the table

    If your ranges will always be the same length, don't create individual names, have one big datarange and then use INDEX functions to get the column and row of interest.

    Steve

Posting Permissions

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