# Click a Map To Filter Excel Data

We’ll set up an Excel workbook so you can click on a map, and see a list of fictional doctors in the state that you selected.

### Set up the Map

First, I found a clip art map of the continental USA, with a separate shape for each state, and pasted that into a workbook.
Next, named the shapes, using the two digit state abbreviation.

1. Click on a state shape, e.g. Washington
2. Click in the Name Box, and type the two digit code – WA
3. Press the Enter key, to complete the naming.

This will be a good test of your geography knowledge and state abbreviation skills.

### Set Up the List of Doctors

On a sheet named Doctors, I entered a list of doctor names, state codes and number of patients.

I named the range, DoctorList, using the formula:
=OFFSET(Doctors!\$A\$1,0,0,COUNTA(Doctors!\$A:\$A),COUNTA(Doctors!\$1:\$1))
as described in my Dynamic Named Ranges tutorial.

### Set Up the Filter Criteria

On the Map sheet, I set up a criteria range for the filter, with the State heading in cell A1, and a State code in cell A2.
To name this range, I selected cells A1:A2 and typed Criteria in the Name box, then pressed the Enter key.

### Set Up the Filter Extract

Next, I copied the headings from the Doctor list, and pasted them below the map.
With the pasted cells still selected, I named that range StateList.
This is where the list of doctors will appear when you click a state in the map.

I added a module to the workbook, and entered some code to use an Advanced Filter, to extract a list of doctors for the selected state.

```Sub GetDoctorList()
On Error Resume Next
Dim strState As String
Dim sh As Shape
Dim wsMap As Worksheet
Dim wsDoctor As Worksheet
Dim wsList As Worksheet
strState = Application.Caller
Set wsMap = Worksheets("Map")
Set wsDoctor = Worksheets("Doctors")
wsMap.Range("A2").Value = strState
Action:=xlFilterCopy, _
CriteriaRange:=wsMap.Range("Criteria"), _
CopyToRange:=wsMap.Range("StateList"), Unique:=False
wsMap.Activate
wsMap.Range("A1").Activate
End Sub```

### Assign the Macro to Shapes

The final step is to assign the GetDoctorList macro to each shape. You could right-click on each shape, click Assign Macro, and select the macro, to do this manually. That might take quite a while for all 48 states, so I wrote a little macro, to do the work for me. You’ll only have to run this once, while setting up the workbook.

```Sub AddMacro()
Dim sh As Shape
For Each sh In Worksheets("Map").Shapes
sh.OnAction = "GetDoctorList"
Next
End Sub```

### Run the Filter

Now, to run the filter, click on a state in the map.
The state code appears in cell A2, and the list of doctors is in the extract range, starting in cell B18.

_________________