Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
best method to determine "most frequent value" from a dynamic list | Excel Discussion (Misc queries) | |||
Making a dynamic list?! | Excel Discussion (Misc queries) | |||
Subtotals in a list | Excel Discussion (Misc queries) | |||
How to Change List Based on Value Chosen in Another List | Excel Worksheet Functions | |||
List Box with dynamic list - Help | Excel Worksheet Functions |