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" <anonymous@discussions.microsoft.com> wrote in message
news:222101c532cb$be6f1cf0$a601280a@phx.gbl...
> 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)+1>COUNTIF(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" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:194301c53269$73231100$a401280a@phx.gbl...
> >> 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....
> >
> >
> >.
> >