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,"-")

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,"-")

Guess you mean you have 2 workbooks. I'd take a couple of minutes to
simplify/consolidate the base data set-up like this: Insert a new sheet in
your "2nd spreadsheet", then do a one-time manual sequential copy n paste
special as values (in row2 down) from each of your identically structured
"numerous pages", stacking the data up (stacking order is immaterial). Then
just paste over the col headers, and you can now easily apply/use autofilter
and do pivot table analysis as well. You could also move over your "1st
spreadsheet" into the same book, and start your formulating from there,
reading the consol data in the new sheet. Much, much simpler this way,
believe me. Hit the YES below, won't you?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Deema" wrote:
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,"-")

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,"-")

receiving a #NAME? error.
=IF(ROWS(D$2:D2)B$2,"",INDEX(Outbound,SMALL(IF(S alesRep=A$2,ROW(Outbound)),ROWS(D$2:D2))-MIN(ROW(Outbound))+1))

#NAME? means Excel doesn't recognize something in the formula. In the
formula I suggested I used named ranges and noted what those named ranges
refer to. So the formula that is returning #NAME? doesn't recognize either
Outbound or SalesRep or both.

Did you create named ranges called Outbound and SalesRep? If not, then
that's the problem.

--
Biff
Microsoft Excel MVP


"Deema" wrote in message
...
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,"-")

Thank you again T. Valko . . . I did set up the name ranges but my formula
contained a misspelling in my range range - Inboud instead of Inbound. Thank
you, this works awesome!!!!

"T. Valko" wrote:

receiving a #NAME? error.
=IF(ROWS(D$2:D2)B$2,"",INDEX(Outbound,SMALL(IF(S alesRep=A$2,ROW(Outbound)),ROWS(D$2:D2))-MIN(ROW(Outbound))+1))

#NAME? means Excel doesn't recognize something in the formula. In the
formula I suggested I used named ranges and noted what those named ranges
refer to. So the formula that is returning #NAME? doesn't recognize either
Outbound or SalesRep or both.

Did you create named ranges called Outbound and SalesRep? If not, then
that's the problem.

--
Biff
Microsoft Excel MVP


"Deema" wrote in message
...
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,"-")

Good deal! Thanks for the feedback.

--
Biff
Microsoft Excel MVP


"Deema" wrote in message
...
Thank you again T. Valko . . . I did set up the name ranges but my formula
contained a misspelling in my range range - Inboud instead of Inbound.
Thank
you, this works awesome!!!!

"T. Valko" wrote:

receiving a #NAME? error.
=IF(ROWS(D$2:D2)B$2,"",INDEX(Outbound,SMALL(IF(S alesRep=A$2,ROW(Outbound)),ROWS(D$2:D2))-MIN(ROW(Outbound))+1))

#NAME? means Excel doesn't recognize something in the formula. In the
formula I suggested I used named ranges and noted what those named ranges
refer to. So the formula that is returning #NAME? doesn't recognize
either
Outbound or SalesRep or both.

Did you create named ranges called Outbound and SalesRep? If not, then
that's the problem.

--
Biff
Microsoft Excel MVP


"Deema" wrote in message
...
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,"-")