Hello T. Valko. Thank you this worked beautifully. If I may . . I have one
additional question.

On Sheet 2: I not only have to bring over the customer name (for each
particular tech) but also the number of OUtbound and Inbound calls.

On Sheet 1: Outbound was in column C and Inbound was in column D.

I tried revising the below formula to bring over the above data but am
receiving a #NAME? error. The below formula is for the

=IF(ROWS(D$2:D2)B$2,"",INDEX(Outbound,SMALL(IF(Sa lesRep=A$2,ROW(Outbound)),ROWS(D$2:D2))-MIN(ROW(Outbound))+1))

I must have something wrong? Any suggestions?? Thank you again!!!!!

"T. Valko" wrote:

Try this...

Names used in the formulas refer to:

SaleRep: refers to =Sheet1!$A$2:$A$100
Customer: refers to =Sheet1!$B$2:$B$100

On Sheet2:

A1 = some name like Wahlberg

Enter this formula in B1. This will return the count of records related to
Wahlberg

=COUNTIF(SalesRep,A1)

Enter this array formula** in C1. This will return the customer names
related to Wahlberg.

=IF(ROWS(C$1:C1)B$1,"",INDEX(Customer,SMALL(IF(Sa lesRep=A$1,ROW(Customer)),ROWS(C$1:C1))-MIN(ROW(Customer))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy the formula in C1 down until you get blanks meaning all recods have
been extracted.

--
Biff
Microsoft Excel MVP


"Deema" wrote in message
...
I have two spreadsheets.

First spreadsheet contains 4 columns of data (A-D). A= sales rep,
B=customer, C=outbound, D=inbound.

Second spreadsheet contains numerous pages, all are indentical except that
each page contain data for a different sales rep.

I am trying to create an IF statement that would pull data from the first
spreadsheet for a particular tech. I tried this put it's not pulling all
of
the data.

Below is basically searching column A for a sales rep by the name Wahlberg
and then if found taking the data in column B (customer) and putting it in
a
column on my second spreadsheet.
=IF(sheet1!$$A$2:$A$200="Wahlberg",sheet1!$B$2:$B$ 200,"-")