assuming your file is not "created" by the external program - i.e. you can
put code etc into the sheet and it will not be overwritten then.....

Setup a 2-column range somewhere and list all the employees is 1st column
and team name in 2nd column. call the range "myteams"

insert the following name in the sheet - call it mynames
this assumes that your "input" names start in Col A row 2

=OFFSET('sheet1'!$A$2,0,0,COUNTA('sheet2!$A:$A),1)

insert a name for Cell A1 - call it "top"

***************

Sub auto_open()
Range("top").Select

Dim name As String
Dim r As Variant

For Each r In Range("mynames")

ActiveCell.Offset(1, 0).Select
name = ActiveCell.Offset(0, 0).Value
ActiveCell.Offset(0, 13).Value = Application.VLookup(name, Range("myteams"),
2, 0)

Next

End Sub

****************
this will populate column "N" for all names based on the values in your table

"bjohn" wrote:

I have a report generated from a program exported into excel upon completion.
When that workbook opens (only using one worksheet named Data), I want to
have the values in column N (Teams) populate based on values in the same row
in column A (Employee). Essentially I have 30 employees broken up into 7
teams.

Since I need to have Column A look at up to 30 values to return column N
values, should I run an If Then or Case, or something else entirely? My Excel
and VBA is very rusty. Need help with correct commands and basic structure,
please.

Code I have now is:

Private Sub Workbook_Open()
If Range("$a2").Value = "Emp1" Then
Range("$n2").Value = "TeamA"
ElseIf Range("$a2").Value = "Emp2" Then
Range("$n2").Value = "TeamB"
ElseIf Range("$a2").Value = "Emp3" Then
Range("$n2").Value = "TeamB"
ElseIf Range("$a2").Value = "" Then
Range("$n2").Value = ""
Else: Range("$n2").Value = "No Team"
End If

End Sub

Code works for row 2 but then stops. I've tried using both absolute and
relative referencing to no avail. Any help is appreciated