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

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
7. Click OK button

### Formula in cell A16

=INDEX(A19:A22,MATCH(C12,A19:A22,0))

### Setting up the chart

1. Create a column chart
2. Right click on column chart and click on "Select Data..."
3. Click on "Edit" button
4. Change series name to A16 and series values to B16:E16 (See picture below)
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
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
DoEvents
Next i
End If
End Sub
```