Thanks Gary, I appreciate your help. I've entered your code but I am getting
an error when it tries to copy.

"rxfr.Copy s1.Range("B1")" gets hung up and I'm not versed in VB code enough
to figure it out.

"Gary''s Student" wrote:

This is just an example that you can adapt to your specific needs. Say that
in Sheet2 the numbers are in column A and and the names are in column B.

In Sheet1 we put the required number in cell A1

In a standard module, put the following VBA code:

Sub xfr()
Dim rxfr As Range
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
v = s1.Range("A1").Value
n = s2.Cells(Rows.Count, 1).End(xlUp).Row
Application.EnableEvents = False
s1.Range("B:B").Clear
Set rxfr = Nothing
For i = 1 To n
If (s2.Cells(i, 1).Value = v) Then
If rxfr Is Nothing Then
Set rxfr = s2.Cells(i, 2)
Else
Set rxfr = Union(rxfr, s2.Cells(i, 2))
End If
End If
Next
rxfr.Copy s1.Range("B1")
Application.EnableEvents = True
End Sub



In the Sheet1 code area put the following event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1")
If Intersect(Target, r) Is Nothing Then Exit Sub
Call xfr
End Sub

The the Sheet2 code area put the following event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A:B")
If Intersect(Target, r) Is Nothing Then Exit Sub
Call xfr
End Sub

Now whenever you change the table in Sheet2 or the lookup value in Sheet1,
the lookup process will refresh.
--
Gary''s Student - gsnu200780


"JeffH" wrote:

I realize autofilter is a way to view data in a worksheet. However, I think
my problem is a little different.

On worksheet 1, I want to input the employer number and have it
reference/list from worksheet 2 all the employees listed by employer number.
Vlookup doesn't work because there are multiple employees per employer
number.

I realize I could go to worksheet 2 and simply do an autofilter, but the
report is on worksheet 1.

I appreciate your help.

"Gary''s Student" wrote:

AutoFilter is the usual way to list all the rows associated with a match to a
given value on a given column.
--
Gary''s Student - gsnu200780


"JeffH" wrote:

I have a worksheet that list all employees by employer number. I have a
second worksheet whereby I would like to enter the employer number and have
all the employees associated with that employer number listed.

How best can I do this?

Vlookup will only provide one employee. There has got to be a simple way to
do this.