VLOOKUP & Dynamic drop down lists

Hello all,

After searching like crazy, I am stuck as to how to create a dynamic drop down list using VLOOKUP.

In one workbook:
- I have an input worksheet where employees enter their data and the data gets transferred to a master-list via button (macro). Every employee row in the master-list has their manager's ID "linked" (via an org list and VLOOKUP)
- As a manager, there is a separate manager worksheet that a) validates the manager's ID and b) builds the drop down list of their team only (this is where I get into some trouble).


Is there a way to build the drop down list in the manager worksheet that's based on the managers ID, matched to the data that is in the master-list?

Please help!

A list like that is less than ideal as the source for data validation lists.

But, if that's what you intend to use....
• I created a tab named "Analysis"
...A1: a manager name...Manager 2

• I put your list in a tab named "Data", beginning in cell A1 (and assuming the headings (Manager Name, etc) are on Row_1)

• I created a tab named "Lists"
On that tab
• A1: Team
• Select A1:A14 and Home.Format-as-table...follow the prompts.
• Rename that table to "tblMgrTeam"....Table_Tools.Design.Table_Name
• Enter this ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER) in A2

Please Login or Register  to view this content.

...Excel will propagate that formula down the table rows

• Formulas.Name_Manager...Click: New
Name: MgrTeamList
Refers to:

Please Login or Register  to view this content.

...Click: OK

On the An tab, create a data validation on cell A2
• Data.Validation.Validation
Allow: List
Source: =MgrTeamList
...Click: OK

Now, whenever the mgr name changes in A1, the name "MgrTeamList" formula will find create a list of team members for that manager.
That list will appear in the data validation dropdown.

Does that help?

Hi Tom,

This works! However, because my data is added ad-hoc, ie/ my manager team list isn't always at a final stage, my data includes #N/A's (because the employee name is matched to their manager name via VLOOKUP), and therefore, the INDEX formula doesn't recognize these items and the drop-down doesn't get built.

Is there a way for the drop down lists to get built even when my data has #N/A's?

Please advise!

Originally Posted by PNY80

Hi Tom,

This works! However, because my data is added ad-hoc, ie/ my manager team list isn't always at a final stage, my data includes #N/A's (because the employee name is matched to their manager name via VLOOKUP), and therefore, the INDEX formula doesn't recognize these items and the drop-down doesn't get built.

Is there a way for the drop down lists to get built even when my data has #N/A's?

Please advise!

Hi Tom - I figured out that by wrapping my VLOOKUP with an IFERROR, the #N/A's are removed and the drop downs operate as expected.

Thanks SO MUCH for your assistance in helping me build my drop downs. It is greatly appreciated!!!