Best done by macro
Sub GenerateSalesRepSheet()
Sheets("Sales Rep Tracker").Rows("8:" & Rows.Count).Clear
For N = 2 To Sheets("SALESMEN").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("SALESMEN").Cells(N, 5) = "" Then
Sheets("Template").Range("A1:J4").Copy Destination:=Sheets("Sales Rep Tracker").Cells(Rows.Count, 3).End(xlUp).Offset(1, -2)
Sheets("Sales Rep Tracker").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0) = Sheets("SALESMEN").Cells(N, 1) & " " & Sheets("SALESMEN").Cells(N, 2)
End If
Next N
End Sub
Open up the VBA editor by hitting ALT F11
Insert a new module by hitting Insert - Module
Paste the macro into the empty sheet
Hit ALT F11 to get back to the worksheet.
Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007.
Bookmarks