Bit clumsy, but can do it for Bob Jim or Dave. Thus to get the row numbers

=IF(ROW(Sheet1!A2)-ROW(Sheet1!$A$2)+1(COUNTIF(Sheet1!$A$2:$A$102,"Bo b")+COU
NTIF(Sheet1!$A$2:$A$102,"Jim")+COUNTIF(Sheet1!$A$2 :$A$102,"Dave")),"",SMALL(
IF(Sheet1!$A$2:$A$102={"Bob","Jim","Dave"},ROW(She et1!$A$2:$A$102),""),ROW(S
heet1!A2)-ROW(Sheet1!$A$2)+1))

I'll work on the exclusive and better formula

--

HTH

RP
(remove nothere from the email address if mailing direct)


"johnT" wrote in message
...
Bob,

Is it possible to search for all salesmen that are not
Bob, Jim or Dave???
(thanks)

-----Original Message-----
Just a couple of points, this formula

=INDEX(Sheet1!B1:B100,A1)

should be abso9lute for copying down

=INDEX(Sheet1!$B$1:$B$100,A1)

and you get extra data in the same sort of way

=INDEX(Sheet1!$D$1:$D$100,A1)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"johnT" wrote in
message
...
yes, this works great...thanks again for all your help!!
-----Original Message-----
John,

I think a bit more information would help, but here is
a
starter

Assuming the salesmen are list in A1:A100 of sheet1

on Sheet2, add this to A1, assuming a salseman of Bob

=IF(ROW(Sheet1!A1)-ROW(Sheet1!$A$1)+1COUNTIF(Sheet1!
$A$1:$A$100,"Bob"),"",S
MALL(IF(Sheet1!$A$1:$A$100="Bob",ROW(Sheet1!
$A$1:$A$100),""),ROW(Sheet1!A1)-
ROW(Sheet1!$A$1)+1))

and copy down to A100 This is an array formula so
commit
with
Ctrl-Shift-Enter.

You now have the row numbers of the matching salesmen
on
sheet1 with no
gaps. You then just get the data like so in B1

=INDEX(Sheet1!B1:B100,A1)

etc.

--

HTH

RP
(remove nothere from the email address if mailing
direct)


"johnT" wrote in
message
...
I have a rather large spreadsheet listing salesmen,
customers, locations etc. I would like to create
separate
worksheets for each salesman listing only data
related
to
that salesman...on each worksheet i want to referance
the
master worksheet, how can i do this without having a
large
group of blank rows??? I think this may be an
application
for array formulas but i need some help.

thanks in advance....


.



.