Making a List

**ChrisKader** · 10-13-2010, 08:59 PM

Hey everyone,

I have a list that is similar to this: Colum A: Employee Name, Column B: Manager.

I am needing to use the data validation feature in excel to make a list of employees depending on what manager they select. There are about 250 employees and 25 managers. Is there an easy way I can do this? I have tried everything: LOOKUP, INDEX, OFFSET and cannot seem to get it.

**Sofistikat** · 10-13-2010, 09:13 PM

Your list will need the Manager in the first cell (whether row or column), with all Employees under them to be listed in subsequent cells after that. You will then be able to use either VLOOKUP (for rows) or HLOOKUP (for columns) to populate a list below your Data Validation control depending on the Manager selected.

A macro to regulate how Employees are listed according to their Manager will help you control your source data.

Please attach an example workbook.

**ChrisKader** · 10-13-2010, 09:18 PM

I am currently using an HLOOKUP but it is not working in the way it needs to. Pretty much, I need to be able to copy my "Roster" with Team Assignments into the sheet and everything kinda "Meld". Currently, I am having to convert all teams from columns into rows.

**Sofistikat** · 10-13-2010, 09:28 PM

Attached is a possible solution.

Hope it helps.

**ChrisKader** · 10-13-2010, 09:51 PM

I want to do a VLOOKUP, but data validation wont let me use that.

**Sofistikat** · 10-13-2010, 10:01 PM

Aha! I think I know what you mean now. Give the attached a go.

**ChrisKader** · 10-13-2010, 10:41 PM

Column A: Agent Name
Column B: Team Manager

Cell C1: TM Name
Cell C2: Agents assigned to this TM.

**Sofistikat** · 10-13-2010, 10:45 PM

Did you try out the attached file in my last post?

Cell J11 allows you to pick a TM Name from a drop-down list, which then makes it possible for you to select Agents assigned to that TM from another drop-down list in Cell J12.

**JBeaucaire** · 10-13-2010, 10:45 PM

I have a pretty cool trick DV formula for putting all the employees for one manager in a DV drop down.

Take a look. There are dynamic named ranges on sheet "Lists" and dynamic DV and Conditional Formatting on Sheet1.

**ChrisKader** · 10-13-2010, 10:58 PM

Dude. I want to kiss your feet right now lol. I am gonna mess with this, but I pasted my roster over yours (Replaced managers and employees with mine) and it looks like it works. THANK YOU!!!!