Sheet A ('Customers Alignment'):
--------------------------------------
column A- name of sales representative
column B - customer ID
(Not sorted in any particular order. So, a sales rep could be listed in row
5, 27, etc)

Sheet B ('Sales Rep Lists'):
------------------------------
cell 'A1' drop-down selection of sales rep name.
I need below that to list the customers of the sales rep selected in cell
A1. Can this be done with a single formula (maybe with an Vlookup and/or an
Offset?)

Crossposted here...

http://www.mrexcel.com/board2/viewtopic.php?t=237863

In article ,
Bill_S wrote:

Sheet A ('Customers Alignment'):
--------------------------------------
column A- name of sales representative
column B - customer ID
(Not sorted in any particular order. So, a sales rep could be listed in row
5, 27, etc)

Sheet B ('Sales Rep Lists'):
------------------------------
cell 'A1' drop-down selection of sales rep name.
I need below that to list the customers of the sales rep selected in cell
A1. Can this be done with a single formula (maybe with an Vlookup and/or an
Offset?)

Bill,
use the following array formula (Shift+Ctrl+Enter) in A2 of Sheet B:

=IF(ISERROR(SMALL(IF('Sheet
A'!$A$2:$A$100=$A$1,ROW($A$2:$A$100)),ROW()-ROW($A$2)+1)),"",INDEX('Sheet
A'!$B$2:$B$100,SMALL(IF('Sheet
A'!$A$2:$A$100=$A$1,ROW($A$2:$A$100)),ROW()-ROW($A$2)+1)))

HTH
Kostis Vezerides

Bill_S wrote:
Sheet A ('Customers Alignment'):
--------------------------------------
column A- name of sales representative
column B - customer ID
(Not sorted in any particular order. So, a sales rep could be listed in row
5, 27, etc)

Sheet B ('Sales Rep Lists'):
------------------------------
cell 'A1' drop-down selection of sales rep name.
I need below that to list the customers of the sales rep selected in cell
A1. Can this be done with a single formula (maybe with an Vlookup and/or an
Offset?)