Very specific dynamic range requirment

[jamesc1987]

Hello.

Across the top of my excel sheet (row 1) I have months listed from September 2008 to September 2016. Down the left hand side of the sheet, I have a long list of countries and regions.

I would like to create a chart that selects some the countries down the left hand side, and only sources september 2008 to the current month (September 2011). However, when October comes I would like the chart to show september 2008 to October 2011 etc..

I have a cell on a data sheet in the workbook which always shows the current month in the format I need. (Currently Sept_2011) if that is useful for defining where they dynamic range should stop.

I think there must be a way to use OFFSET and COUNTIF to determine where the dynamic range should look, but try as I might I'm stuck.

Any help is much appreciated

 

[WinteE]

Hi,

This is not possible with formulas, as you have to change the data range of the chart.

The code below changes the data range based on a column number (formula =COLUMN() ).
It is not easy to explain, but I guess you'll understand what I mean.

Sub ChangeChartRange()
    
    i = 5  'this is the column number
    
'LastCol is the column reference ( 1 = A, 2 = B etc.)
    If Int(i / 26) = 0 Then
        LastCol = Chr(i - (Int(i / 26) * 26) + 64)
    Else
        LastCol = Chr(Int(i / 26) + 64) & Chr(i - (Int(i / 26) * 26) + 64)
    End If
    
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartArea.Select
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A2:" & LastCol & "5"), PlotBy:= _
        xlRows
End Sub