When working with Excel it could be useful to select data from a drop down list of options and have that selection further filter information available from another drop down list.
E.g. you could select the application name such as Excel and a second drop down list would only show the relevant topics from the Excel bank of topics. A Word list would only show topics from the Word only list, etc.
[ Video tutorial produced by Activia Training and purchased by ZandaX ]
In the image below, Word has been selected from the first drop down list:
The list below that will then only show topics from the Word course:
In order to recreate this, create a sheet with all the data listed that you would like to create a list from. Your headings will be listed in column A e.g. Word, Excel, PowerPoint, Access etc.
The subsequent columns contain the data for each option e.g. column B lists the information required for Word, column C lists the Excel data, etc.
Next we would need to name the ranges. Highlight cells B1 to B5 and select Define Name on the Formula Ribbon. Name it Word and click on OK. Repeat these steps for columns C to F with the respective names.
The second drop down uses the INDIRECT Function needs to get the data from the first drop down as below using the INDIRECT Function in the Data Validation settings.
So this will get the data from B2, it sees its content is "Word" and then the list is taken from the correct column offset position. So it would be 1 in this case. If we chose say Windows that would be 8 cells down so it will get the topics out of the 8th column across for the drop down list.
Clever I trust you will agree so now you can get drop downs to talk to each other.
Want Better Excel Skills?
We have online courses with full 12-months' access. RRP from $49 limited time offer just $12.00
Get a solid foundation in Project software to create solid, resilient project plans.
You don't need prior experience with Project: just be able to use a PC with Microsoft Windows.
Microsoft Project Advanced
The Advanced course takes you to a level that will put you in complete control of your projects.
You should, of course, be fully conversant with the skills and concepts taught in the Introduction course.
Become a Visio master with our two intensive but very easy-to-follow CPD certified Microsoft Visio courses.