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....


.



.