Create a consolidated list

Hello all,

This should doable, but I can figure out how. On the attached sample workbook, I have two worksheets. One worksheet (Customer List) has a complete listing of customers. The other (Customers by Rep) has a drop down box to select a sales rep. What I would like to do, is once a sales rep selected from the drop down box (cell A2) a list of customer numbers (column A from 'Customer List') associated with that rep is populated in a list starting at cell A5 and continuing to include all of that rep's customers (number of customers will vary depending on sales rep).

Thanks in advance for your time and your help

Sample Workbook.xlsx

Thanks popipipo, I don't know a lot about pivot tables, but I don't think so. I will have more columns in the final workbook that are driven from the customer numbers in column A. I don't know how to create a formula from a pivot table.

Thanks, I am applying your suggestion to my worksheet now. I'll see if I can make it work.

I've attached your workbook with those formulae in.

Hope this helps.

Pete

Originally Posted by popipipo

You can use a pivot table

^^probably the easiest and most logical

Well, that does what I need it to do! Thanks Pete

You're welcome - thanks for feeding back.

Pete

Put this formula in cell D3 of the Customer_List sheet:

=IF(C3='Customer by Rep'!$A$2,COUNTIF(C$3:C3,C3),"-")

Then copy it down to the bottom of your data (or beyond, to allow for new data to be added).

Then in A5 of the Customer_by_Rep sheet you can have this formula:

=IFERROR(INDEX('Customer List'!A:A,MATCH(ROWS($1:1),'Customer List'!$D:$D,0)),"")

and copy this down as far as you need to (e.g. to row 250 to accommodate HOUS). If you want the customer names as well, copy/paste A5 into B5, then copy that down.

Then just change the rep in your drop-down in A2 and see the list change automatically.

Hope this helps.

Pete

Also a nice solution, thanks newdoverman