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.
- Begin by making a list of the parameters you need in each column of a blank sheet. You can make as many columns as required for the project, but remember that each column is dependent on the one right before it. So for example, the column structure might look like this.
- Raw Materials with a list of the type of supplies. For example, sugar, flour, salt, milk.
- When one of the raw ingredients is chosen, the next list will show Supplier Names for that particular ingredient.
- If the cake company has two different bakeries, then the next list might include the Factory Names by location. This would be the place to where the ingredients are being shipped by the specific supplier.
- =INDIRECT(A2)
- A2 refers to the cell in the column for sugar
- if the cell were flour, it would be A3 and so on
- =INDIRECT(SUBSTITUTE(A2&B2))
- where A2 and B2 are the respective cells that associate with Sugar and Sandy’s Sugar Co. (the specific Supplier Name).
- Now, make a drop-down list for the first table (Raw Materials). Register or connect the table with the proper cell by using Data Validation under the Data Tools. Each step must be done in the right order, or your columns, tables and lists will not be associated with the correct data.
- Next, take the very next column that refers to the specific raw material such as Sugar. This column will store the name of the suppliers where the sugar is purchased. Click Data Validation and the dialog box will open.
- Be sure the Settings tab is open, and choose List in the first drop-down box.
- In the Source field, this is where it connects to the first table or column. So the supplier names listed match up to sugar. To do this, type the following into the box.
- To complete the last column or table being the Bakery Location, this must depend on the second column, which depends on the first. Use this code to connect them all together.
- The Comprehensive Guide to the Excel Ribbon: Making the Most of Your Data - January 31, 2023
- 51: Oz du Soleil & the Global Excel Summit 2021 - February 8, 2021
- 50: Randy Austin – Excel for Freelancers - January 22, 2021
