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