Now for something really cool: Make a drop-down menu that changes dependent on what you choose in another cell. It only takes five minutes!
1. Enter the data (2 minutes)
The main category should be in column A and the sub-categories in the other columns.
Important: The headline of each sub-category must be exactly the same as the name in the main category!
2. Name the ranges (1 minute)
Select all the entries in a column and use the shortcut Ctrl+Shift+F3 to create a name for the range. Repeat this with every column.
3. Make the drop-down menu for the main categories (1 minute)
Select a cell, e.g. I3, and choose Data Validation on the Data tab (or shortcut Alt+D+L). Choose List, put the cursor in the Source field and press F3 to open the Paste Name window. Choose Categories.
4. Make the drop-down menu for the sub-categories (1 minute)
Select a cell, e.g. J3, and choose Data Validation on the Data tab (or shortcut Alt+D+L). Choose List, put the cursor in the Source field and type this formula:
We have created a dynamic drop-down menu! Can you imagine any easier way to impress your colleagues?
Author: Audun Danielsen