Author: Oscar Cronquist Article last updated on December 23, 2018

animated chart2
The drop down list lets you select a region. An event macro checks if there is a new drop down list value. It then compares old and new series values so it can calculate new values, in small steps.

Build a drop down list

  1. Select cell C12
  2. Go to tab "Data" on the ribbon
  3. Click on "Data Validation button
  4. Select tab "Settings"
  5. Select "List" in the drop down list "Allow:"
  6. Type North, East, South, West in Source field
    animated chart - drop down list
  7. Click OK button

Formula in cell A16


Setting up the chart

  1. Create a column chart
  2. Right click on column chart and click on "Select Data..."
    animated chart - select data source
  3. Click on "Edit" button
  4. Change series name to A16 and series values to B16:E16 (See picture below)
    animated chart - add series
  5. Click OK
  6. Click OK

Event Code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Single, j As Single, x As Integer
If Target.Address = "$C$12" Then
    cht = Range("B16:E16").Value
    On Error Resume Next
        x = WorksheetFunction.Match(Range("C12"), Range("A19:A22"), 0) - 1
        If Err > 0 Then Exit Sub
    On Error GoTo 0
    Nval = Range(Cells(19 + x, "B"), Cells(19 + x, "E")).Value
    For i = 1 To 4
        Nval(1, i) = (Nval(1, i) - cht(1, i)) / 10
    Next i
    For i = 1 To 10
        For j = 1 To 4
            cht(1, j) = cht(1, j) + Nval(1, j)
        Next j
        Range("B16:E16") = cht
    Next i
End If
End Sub

Download Excel file


Functions in this post

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

MATCH(lookup_value, lookup_array, [match_type]
Returns the relative position of an item in an array that matches a specified value