Dynamic territory list
 

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?

Dynamic territory list
 

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(INDIR ECT("'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,MATC H(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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bill_S" wrote:
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?

Dynamic territory list
 

Here's a sample file:

sample_extract.xls 14.5kb

http://cjoint.com/?kleLZfCGvN

Biff

"Bill_S" wrote in message
...
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?