Overview
※ Download: Conditional drop down list in excel
Convert data table into Excel tables Instead of converting the entire data table as one Excel table, convert it into 3 Excel tables. Do the same for the Games and Utility columns and rename them to TableGame and TableUtility. In this case, this is dependent on the selected Division by the user.
Is there any way that it works across all the sheets? The first drop down is being copied but the dependent one in next cell is never. But, to accomplish exactly what you are asking, we would need to turn to macros.
How to Create a Drop Down List in Excel (the Only Guide You Need) - For example, create ranges named CanadaWest and USAWest. Name the Fruit and Vegetable Lists Next, follow these steps to name the fruit and vegetable lists.
To create these dependent drop-down lists, execute the following steps. On the second sheet, create the following. Name Range Address Food A1:A3 Pizza B1:B4 Pancakes C1:C2 Chinese D1:D3 2. On the first sheet, select cell B1. On the Data tab, in the Data Tools group, click Data Validation. The 'Data Validation' dialog box appears. In the Allow box, click List. Next, select cell E1. In the Allow box, click List. Result: Explanation: the function returns the reference specified by a text string. For example, the user selects Chinese from the first drop-down list. As a result, the second drop-down lists contains the Chinese items.
Note that with this approach, if you change the table name you would also need to update the related data validation formulas. So let us start on how to create a dependent or conditional drop down list with a simple example. The detailed steps follow below: 1. This logic is the same with Data Validation as well which can be used to our advantage. Download the Example File How does this work. Plus, you can remove blank choices from the drop-down. The displayed list of regions is unique for each country but it does not depend on the selection in the first drop-down list. That way, it can be used in multiple columns to refer to the value in the column to the left relativerather than in the original column absolute. Demo: Dependent Drop Down Lists This animated screen shot shows how a simple dependent drop down list works. The first INDEX function returns the upper-left cell, and the second INDEX function returns the lower-right cell.
