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.
MapUSA

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
    MapUSA_NameWA
  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.
MapUSA_Docs
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.
MapUSA_Crit

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.
MapUSA_Extr

Add the Filter Macro

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
wsDoctor.Range("DoctorList").AdvancedFilter _
  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.
MapUSA_Filter
_________________