Dynamic Lists in Excel based on an input criteria

New Contributor

Hello Community


I have a problem to solve and hope you can help me.


Background: This is an interactive template where users can enter up to 6 goals. The user then has the option of adding 5 objectives per goal. Refer image 1.


Thereafter, the user can enter an unlimited number of tasks that supports each objective and subsequently the goal. This is on a different sheet and the Goal Alignment column has a list based on all the goals the users have entered elsewhere. Refer Image 2.



I have a second column (Refer Image 3) that is referring to the objectives the users have entered. Currently, the cell is pointing to a long list of 30 potential objectives. From a user perspective this would be frustrating as they would have to scroll forever to get to where they need to be and it will also give them decision fatigue, as they need to decide between 30 objectives as opposed to 5 objectives based on the goal that they selected in the cell to the left.


So, I would like the objectives cell to point to a specific list of 5 potential objectives based on the goal they selected in the column to the left. So, if I select Goal 1 from the drop down in the Goal Alignment column, the drop down list on display in the Objective cell should only display the objectives that was originally linked to Goal 1. Any suggestions?


User enters Goal and corresponding objectivesUser enters Goal and corresponding objectivesList 1 - User can select only what they entered beforeList 1 - User can select only what they entered beforeList 2- every objective for every goal in one list (Not Ideal)List 2- every objective for every goal in one list (Not Ideal)







2 Replies
Looks like you need a Dependent Dropdown List.
Google it and you'll find a lot of articles on how to do it. (easier if you have Excel 365)

Thank you so much!!!