Dropdown Box Returning Multiple Values

**roycey** · 04-26-2013, 10:26 AM

Hello,

I am working on a sheet that has a dropdown box with managers' names and what I want is for the list of employees to populate the cells beneath the manager's name depending on which manager is selected. I have mixed success with the solution in the link below, but it does not seem to return the corresponding cells.

http://www.excelforum.com/excel-form...one-value.html

I have attached my worksheet (no confidential information). The work I am trying to do is in "input2" and the information that should be pulled from is in administration. Any help would be greatly appreciated!

**Tony Valko** · 04-26-2013, 10:41 AM

Try this...

Entered in D9 and copied down until you get blanks:

=IF(ROWS(D$9:D9)>COUNTIF(Administration!D$4:D$100,D$6),"",INDEX(Administration!E$4:E$100,MATCH(D$6,Administration!D$4:D$100,0)+ROWS(D$9:D9)-1))

Bill Yerds? I know him well!

**roycey** · 04-26-2013, 10:47 AM

That works! Thank you so much!

**Tony Valko** · 04-26-2013, 10:49 AM

You're welcome. Thanks for the feedback!

**Pete_UK** · 04-26-2013, 10:55 AM

I'm not sure where you would get some of the data from for the Input (2) sheet, i.e. columns H I and J, as you don't have these in the Admin sheet, and presumably cell D6 should be a drop-down to select the manager's name.

This can be done quite easily by using helper columns (e.g. column C in both sheets) to identify the appropriate records, and then an INDEX formula can extract the appropriate data. The attached file shows the basics of how to do this - just choose the manager's name in D6 and the data will automatically change.

Hope this helps.

Pete