Dynamic territory list

[Guest]

Sheet1 named "Alignment" (contains alignment of our sales people):
---------------------------------------------------------------------------
Column A= Customer List of ID's
Column B= Salesperson 1 ("yes" in cells below if he has that customer)
Column C= Salesperson 2 ("yes" in cells below if he has that customer)
Column D= Salesperson 3 ("yes" in cells below if he has that customer)

Sheet2 named "Customer Listing":
---------------------------------------
Cell A1= drop-down selectable list of our sales people
When a salesperson is selected I need a list of all their customers to
appear on the sheet.

I would prefer to do this with formulas rather than code. I remember
reading somewhere a while back that with dynamic ranges and maybe the
Offset(?) function this could be done formulaically. Is that possible?

 

[Guest]

One way using non array formulas to achieve it ..

In sheet: Alignment,

Assume CustIDs are listed in A2 down (A1 contains a label, say: CustID),
with sales persons listed in B1 across, eg:

CustID........SalesP1......SalesP2
1111..............Yes
1112..............................Yes
1113..............Yes
1114..............................Yes
etc

In sheet: Customer Listing,

Select A1
Click Data > Validation
Allow: List
Source:
=OFFSET(INDIRECT("'Alignment'!B1"),,,,COUNTA(INDIRECT("'Alignment'!1:1"))-1)
Click OK. That will produce in A1, the DV droplist for all the sales persons
(from Alignment's row1).

Then place in A2
=IF(ROW(A1)>COUNT(B:B),"",INDEX(Alignment!A:A,MATCH(SMALL(B:B,ROW(A1)),B:B,0)))

Put in B2:
=IF(OFFSET(Alignment!A:A,,MATCH($A$1,Alignment!$1:$1,0)-1)="Yes",ROW(),"")

Select A2:B2, copy down to cover the max expected extent of data in
Alignment, say down to B200. Hide away col B. Col A will return the required
results, ie the list of CustIDs for the salesperson selected in the droplist
in A1, with all results neatly bunched at the top.

 

[Biff]

Here's a sample file:

sample_extract.xls 14.5kb

http://cjoint.com/?kleLZfCGvN

Biff