Autolist Based on Rep

[matthewcantrell]

I'm attempting to make a list that auto populates based on a selected name. In other words, I have a large data base of customers in one workbook(each of the customer data has a marketing rep attached to it is an adjacent cell); I'm making another file that has a list of marketers. I'm trying to make the the marketer file populate based on the name of the marketer from a drop down with the data source. Any suggestions?

[NBVC]

Say your list is in '[WorkbookA.xls]Sheet1'!A1:A100

And your drop down is in '[WorkbookB.xls]Sheet1!A1

Then in another column of WorkbookA, use formula:

=Sumproduct(--($A$1:$A1='[WorkbookB.xls]Sheet1'!$A$1)) copied down

In WorkbookB, B1 enter formula=Sumproduct(--('[WorkbookA.xls]Sheet1!A1:A100=A1))

This should give count of marketers in WorkbookA.

Then in WorkbookB, A2,

=IF(Rows($A$1:$A1)>$B$1,"",Index('[WorkbookA.xls]Sheet1'!A$1:A$100,Match(Rows($A$1:$A1),'[WorkbookA.xls]Sheet1'!$C$1:$C$100,0)))

copied down as far as you want and across if necessary.

Adjust all ranges to suit.