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