Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Auto Update Graph (Excel 2002)

    Hi

    In the attached workbook the firs tab shows a graph and the subsequent tab containing the data.

    The graph shows a rolling 12 months of data, every month I have to change the range to keep the 12 month trend.

    This in itself is no big deal, except I have about 30 graphs in the real workbook.

    If possible I would like to use a macro to recreate this range at the change in every month.

    Many thanks


    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Auto Update Graph (Excel 2002)

    You can base the chart on dynamic ranges. In the attached version, three dynamic ranges have been defined in Inseet | Name | Define: one for the x-axis and one for each data series. I changed the labels in row 5 to dates formatted as mmm, to calculate the offset in months between the current month and the month in B5.

    If you change the date under "Current" (it now is a formula =TODAY()), you will see the chart change automatically.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Auto Update Graph (Excel 2002)

    Hi Hans

    Once again I am in your debt

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Auto Update Graph (Excel 2002)

    Hi Hans

    Your kindly solved this problem for me re autoupdate graph.
    However when I try to apply it to my master sheet the in the Category(x)axis label contains the a cell range instead of the dynamic range.

    You do not appear to be able to enter it manualy so how is it applied please.


    Many Thanks Braddy
    If you are a fool at forty, you will always be a fool

  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: Auto Update Graph (Excel 2002)

    I don't understand.

    From your attachement "picture", it seems to contain the named range which (in Hans' attached file is the dynamic range)

    You can enter them manually by entering the workbook name and the range name or even with the sheet name and the named range (though excel will convert it to workbook and range name. [Don't forget the exclamation (!) and if the name has a space to "wrap it" in single quotes (')]

    Steve

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

    Re: Auto Update Graph (Excel 2002)

    To enter the range manually, clear the contents of the Category (X) axis labels box, and enter either
    =SheetName!RangeName
    where SheetName is the name of the worksheet that contains the dynamic range, and RangeName is the name of the dynamic range, or
    ='WorkbookName'!RangeName
    where WorkbookName is the name of the workbook that contains the dynamic range. In the example I attached higher up in this thread, it would be either
    =PortFolio!XLabels
    or
    ='Alan Graph Query Tester.xls'!XLabels

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Auto Update Graph (Excel 2002)

    Hi Steve

    Your correct my screenshot was from Hans edited file, that was jus to point out what I was trying to achieve.
    The reason I believed that I could not enter it manually is shown in the screen shot below,

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  8. #8
    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: Auto Update Graph (Excel 2002)

    It looks like you forgot the ".xls" in the file name or if Portfolio is a sheet name, that the range is not on that sheet (which I don't think it is, if it is a dynamic...)

    Steve

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

    Re: Auto Update Graph (Excel 2002)

    Have you defined a name XLabels in your workbook?

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Auto Update Graph (Excel 2002)

    Hi Hans

    I have defined defined a name see screenshot.

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Auto Update Graph (Excel 2002)

    Hi Hans

    I am feeling rather foolish I named the ranges but without selecting them properly. It now works OK

    My apologies once again to you and Steve.

    Braddy
    If you are a fool at forty, you will always be a fool

  12. #12
    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: Auto Update Graph (Excel 2002)

    One "trick" I use to check these types of dynamic ranges is:
    while in the insert name dialoge box
    put the cursor in the "refers to" box

    The dynamic range will be "drawn" with dotted lines around it. If you don't see a range, there is a problem with the definition. Excel will accept formulas, that do not yield a range (eg the number of columns or rows = 0) and even errors without giving any indication the range is undefined...

    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
  •