Contents
Method 1. Function INDIRECT (INDIRECT)
This focus is based on the application of the function INDIRECT (INDIRECT), which can do one simple thing – convert the contents of any specified cell to the address of a range that Excel understands. That is, if the cell contains the text “A1”, then the function will result in a link to cell A1. If the cell contains the word “Masha”, then the function will return a link to a named range with the name Masha etc. Such a kind of “translation of arrows” 😉
Take, for example, this list of Toyota, Ford, and Nissan car models:
Select the entire list of Toyota models (from cell A2 and down to the end of the list) and give this range a name Toyota tab formula (Formulas) through Name Manager (Name Manager). Then we will repeat the same with the lists of Ford and Nissan models, naming the ranges accordingly. Ford и Nissan.
When setting names, remember that range names in Excel must not contain spaces, punctuation marks, and must begin with a letter. Therefore, if there was a space in one of the car brands (for example, Ssang Yong), then it would have to be replaced in the cell and in the range name with an underscore (ie, Ssang_Yong).
Now let’s create the first drop-down list for choosing a car brand. Select an empty cell (green in the picture above) and press the button Data validation (Data Validation) tab Data (Date). Then from the drop down list Data type (Allow) select option List (List) and in the field Source (Source) select the cells with brand names (yellow cells in our example). After clicking on OK the first dropdown list is ready.
Now let’s create a second drop-down list, which will display the models of the brand selected in the first list. As in the previous case, select an empty cell where there should be a list of models (in the picture it is blue) and open the window Data checks, but in the field Source you will need to enter the following formula:
=INDIRECT(F2)
where F2 is the address of the cell with the first drop-down list (replace with your own).
All. After clicking on OK the contents of the second list will be selected by the name of the range selected in the first list.
Cons this way:
- It is necessary to create many named ranges by hand (if we have many car brands).
- Secondary (dependent) ranges cannot be dynamic ranges specified by formulas like DISPOSAL (OFFSET). For the primary (independent) list, they can be used, but the secondary list must be defined rigidly, without formulas. However, this limitation can be circumvented by creating a brand-model matching directory (see Methods 3 and 4).
- The names of the secondary ranges must match the items in the primary dropdown list. Those. if it contains text with spaces, you will have to replace them with underscores using the function SUBSTITUTE (SUBSTITUTE), i.e. the formula will look like:
=INDIRECT(SUBSTITUTE(F2;” “;”_”))
Method 2. Smart tables
This approach is very similar to the previous one, but uses “smart” dynamic tables instead of named ranges, which will act as a data source for the secondary list:
Thus, we:
- First, let’s convert our directories into “smart” tables using the keyboard shortcut Ctrl+T or command Home – Format as a table (Home — Format as Table) и
- We give them namesFruits, Vegetables, Greens) on the tab Constructor (Design) in the field Table name (Table Name).
- We create the first and second (linked) drop-down lists in the same way as in the previous method with the INDIRECT function.
The main advantage and difference of this method is the ability to easily add new products to each category – the dynamic “smart” table will automatically expand, and we do not have to manually correct the link to the range (as required in the previous method).
Method 3. Sorted directory
This method requires a sorted list of brand-model matches like this:
To create a primary drop-down list of brands, you can use the usual method described above, i.e. select on tab Data Command Data validation and specify yellow cells with stamps as a source
But for the dependent list of models, you will have to create a named range with the function DISPOSAL (OFFSET), which will dynamically refer only to model cells of a certain brand. For this:
- Press the keyboard shortcut Ctrl+F3 or use the button Name Manager (Name manager) tab formula (Formulas).
- Create a new named range with any name (for example Models) and in the field Link (Reference) at the bottom of the window, manually enter the following formula:
=СМЕЩ($A$1;ПОИСКПОЗ($G$7;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$G$7);1)
=OFFSET($A$1;MATCH($G$7;$A:$A;0)-1;1;COUNTIF($A:$A;$G$7);1)
Links must be absolute (with $ signs). After pressing Enter sheet names will be automatically added to the formula – don’t be alarmed.
It works as follows. Function DISPOSAL (OFFSET) can give a link to a range of the desired size, shifted relative to the original cell by a given number of rows and columns. In a more understandable version, the syntax of this function is as follows:
=OFFSET(start_cell; shift_down; shift_right; range_height_in_rows; range_width_in_columns)
Thus:
- starting cell – we take the first cell of our list, i.e. A1
- shift_down – the function considers us MATCH, which, simply speaking, gives the serial number of the cell with the selected brand (G7) in the given range (column A)
- shift_right = 1, because we want to reference the models in the adjacent column (B)
- range_height in_rows – calculate using the function COUNTIF (COUNTIF), which can count the number of values u7buXNUMXbmeet in the list (column A) we need – car brands (GXNUMX)
- range_width_in_columns = 1, because we need one column with models
The end result should be something like this:
It remains to add a drop-down list based on the created formula to cell G8. For this:
- select cell G8
- select on the tab Data (Date) Command Data validation (Data validation)
- select the test option from the dropdown list List (List) and enter as Source (Source) = and the name of our range, i.e. = Model
And our linked list of models is ready.
Method 4. Unsorted directory
The previous method is not bad, but it requires the presence of a necessarily sorted reference book of the brand-model type, where the brand names are repeated many times. Sometimes it is required to use a more banal construction as a reference:
The first level of the drop-down list (range A2:A14 in the figure above) is done here in a classic way through the command Data – Data Validation – List (Data — Validation — List) and specify the green cells with category names as the source.
And for the linked dropdowns in the second column (B2:B14) in the field Source (Source) we use a tricky formula:
=СМЕЩ($E$2;1;ПОИСКПОЗ(A2;$E$2:$G$2;0)-1;СЧЁТЗ(СМЕЩ($E$2;1;ПОИСКПОЗ(A2;$E$2:$G$2;0)-1;10;1));1)
It searches for the desired category in the green header of the table, moves down one cell and then refers to a range that is equal in height to the number of products in the selected category.
- 4 Ways to Create a Dropdown List in Sheet Cells
- Auto Generate Dropdown Lists with PLEX Add-In Tools
- Selecting a photo from the dropdown list
- Dropdown list with automatic deletion of already used items
- Dynamic selection of data for a drop-down list using the INDEX and MATCH functions