Dynamic customer list

[bill_s1416]

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?

 

[acw]

Hi

Here's one way

Assuming that Alignment row 1 has headings, with ID in A1 and the list of sales reps in B1, C1.... Create a defined name called reps that covers the list of reps in row 1.

Also in Alignment, create a defined name called data with the refers to formula
=OFFSET(Alignment!$A$1,0,MATCH('Customer Listing'!$A$1,reps,0),COUNTA(Alignment!$A:$A),1)

Customer Listing!A1 has a data validation which uses the defined name reps as the list source.

Make a selection from the listing.

In Customer Listing!B3 array enter the formula

=IF(ROW()-3>COUNTIF(data,"yes"),"",INDEX(Alignment!A:A,SMALL(IF(data="yes",ROW(data),""),ROW()-3)))

Copy down as required and it should bring back the IDs from Alignment column A for the relevant sales reps. You will have to use a VLOOKUP to convert the ID to a name.


HTH

Tony