Excel data validation not only includes controlling a specific cell, but it also allows for creating a secondary drop-down list that is completely dependent on the first or primary list. In other words, once the first list is displayed, and a parameter chosen, then a subset might exist for any given parameter. By further defining an entry, the data can be brought down its most basic form. This is particularly useful when maintaining customer lists where templates are much superior to having users keypunch all kinds of tedious information that relates to every entry. Using the Indirect Function, you will have more control over your workbook templates, thus, providing optimal performance for data entry.
An excellent example for the use of dependent tables is maintaining inventories. Let’s say, for example, you sell craft items. You might offer two different bags of one-inch felt animals. It doesn’t make any sense to create separate cells for each bag quantity. Instead, choose from the first table which would be the list of supplies for sale, then once one-inch felt animals are chosen, another list would drop down showing the bag with 50 animals and then, the bag with 100 animals. The same can be done for handmade coats. You might the sell the same style, but in different sizes. The style is found in the primary drop-down list, while the size is found in the dependent list.
Basically, dependent drop-down lists organize your data, removing the room for error. They are also quite helpful to reduce boredom when entering large amounts of data. Having to keypunch the same numbers and names over and over is a tiresome task. By taking the time in the beginning to configure the appropriate parameters, the job becomes much quicker and easier. Using templates to populate spreadsheets is a clean and efficient method of storing dynamic information. Dynamic generally refers to data that is always changing, either through modified lists or the spreadsheet itself being added to on a regular basis.
As with primary drop-down lists, making secondary or dependent drop-down lists in Excel requires using the Data Validation section of the software. I have an excel tutorials section that covers similar topics. Once that section is found, the first table and list should be defined and created, then the second table and list, which is completely dependently on the first, is built by using a function called INDIRECT.
Following are the steps required in order to create a dependent drop-down list.
Please log in again. The login page will open in a new window. After logging in you can close it and return to this page.