Excel 2007 : Specify Chart Data Range using an IF Formula

Please try the attached. It uses an event drive macro to recreate the chart when the value of the Area/Region cell is changed.

Code below.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range

ActiveSheet.ChartObjects("Chart 3").Activate
For N = 1 To ActiveChart.SeriesCollection.Count
    ActiveChart.SeriesCollection(1).Delete
Next N


For Each Cell In Target
    If Cell.Address = "$C$4" Then
        Select Case Cell.Value
            Case Is = "Region"
                For N = 3 To 6
                    ActiveChart.SeriesCollection.NewSeries
                    ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Name = "='Dynamic Data'!R10C" & N
                    ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Values = "='Dynamic Data'!R11C" & N & ":R12C" & N
                    ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).XValues = "='Dynamic Data'!R11C2:R12C2"
                Next N
            Case Is = "Area"
                For N = 7 To 16
                    ActiveChart.SeriesCollection.NewSeries
                    ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Name = "='Dynamic Data'!R10C" & N
                    ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Values = "='Dynamic Data'!R11C" & N & ":R12C" & N
                    ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).XValues = "='Dynamic Data'!R11C2:R12C2"
                Next N
        End Select
    End If
Next Cell
End Sub