Help on using drop down list choice to populate adjacent col

[mishmish3000]

Hello! A coworker just asked me a good question: in col. A, he has a drop down list of all 95 Tennessee counties. If a user chooses Anderson County from the list, he'd like the entry in col. B to show the region Anderson County is in (we have 13 health regions in Tennessee). He has a list on a separate sheet of all counties and all regions. How would we go about programming Excel 2007 to help with this? I'll attach an example.
Thanks!!!! And Happy Friday!
MishMish3000

[HansV]

Option 1: In B2, enter the formula

=IFERROR(VLOOKUP(A2,Lists!$A$2:$B$96,2,FALSE),"")

and fill down as far as you think the data will go. You have some superfluous formulas, but no code is needed.

Option 2: right-click the sheet tab of the DataEntry sheet.
Select View Code from the popup menu.
Enter or copy the following code into the worksheet module that appears:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range
  If Not Intersect(Range("A:A"), Target) Is Nothing Then
    Application.EnableEvents = False
    For Each rng In Intersect(Range("A:A"), Target)
      If rng.Value = "" Then
        rng.Offset(0, 1).ClearContents
      Else
        rng.Offset(0, 1) = Application.WorksheetFunction.VLookup(rng.Value, _
          Worksheets("Lists").Range("A1:B96"), 2, False)
      End If
    Next rng
    Application.EnableEvents = True
  End If
End Sub

[StuartR]

You can put the following formula in B2 and drag it down
=VLOOKUP(A2,Lists!A2:B96,2,FALSE)

You may also want to give the range Lists!A2:B96 a name and use that instead of the cell reference

Edited to add
As usual Hans is there first, and with a more complete answer.

[mishmish3000]

MOST Excellent, thanks so much, guys!!!