Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Trailing 26 Periods for Charting

    I have a table to which I add bi-weekly data in a new row and a chart based on the table which displays the most recent 27 bi-weekly period results. What functions can you suggest I try to get the chart data to dynamically adjust to the new range?

  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
    I would use OFFSET function in a named formula, and use that name to define the range in the chart. Without more specifics it is difficult to give more details. Perhpas the examples at http://www.contextures.com/xlNames02.html and http://peltiertech.com/WordPress/dyn...t-source-data/ can be of further assistance.

    Steve

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Arcturus,

    Try this. As you enter more data to the table, the chart will automatically update.

    HTH,
    Maud

    AutoChart1.png

    Code:
    Private Sub Worksheet_change(ByVal Target As Range)
    Dim Data As Range
    '---------------------------------------------------------------
    'DETERMINE IF SELECTED CELL IS IN RANGE
    If Target.Column <= 2 And Cells(Target.Row, 1) <> "" And Cells(Target.Row, 2) <> "" Then
        Set Data = Range("a2").CurrentRegion  'SET RANGE
        Data.Select
        Data.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes 'SORT NEW DATA
        ActiveSheet.Shapes(1).Select
        ActiveChart.SetSourceData Source:=Range(Data.Address) 'UPDATE CHART
        Cells(ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).Select
    End If
    End Sub
    Attached Files Attached Files

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Arcturus,

    After realizing that you wanted the chart to update with only the 27 most recent data results, I revised the code to do so. With each new entry the chart will update with the last 27 biweekly periods.

    AutoChart2.png
    Attached Files Attached Files

Posting Permissions

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