Task: create a drop-down list in a cell for easy entry of information. The options for the list should be taken from the given dynamic range, i.e. if tomorrow they make changes to it – for example, they remove unnecessary elements or add a few more new ones – they should be automatically reflected in the drop-down list:
Method 1: If you have Excel 2007 or later
A simple and convenient way with almost no formulas. Uses a new feature of the latest versions of Microsoft Excel starting from the 2007 version – “Smart Tables”. Its essence is that any range can be selected and formatted as a Table. Then it turns, to put it simply, into “rubber”, that is, it starts to track changes in its size, automatically stretching and shrinking when data is added or removed to it.
Highlight the range of options for the dropdown list (A1:A5 in our example above) and on Home tab click Format as a table (Home – Format as Table). You can choose any design – it does not matter:
Please note that the table must have a title line (in our case it is A1 with the word Employees). The first cell plays the role of a “header” and contains the name of the column. On the tab that appeared after turning into a Table Constructor (Design) you can change the default table name to your own (no spaces!). By this name, we can then address the table on any sheet of this book:
Now select the cells where you want to create drop down lists (in our example above it is D2) and select in older versions of Excel from the menu Data – Check (Data – Validation), and in new press the button Data Validation tab Data (Date). In the window that opens, on the tab Settings select option List and enter in the field Source here is the formula:
=INDIRECT(“Table1[Employees]”)
=INDIRECT(“Table1[Employees]”)
The meaning of this formula is simple. Expression Table1[Employees] is a link to a column with data for the list from our smart table. But the problem is that for some reason Excel does not want to understand direct links in the field Source, i.e. you cannot write in the Source field an expression like =Table1[Employees]. Therefore, we go for a tactical trick – we enter the link as text (in quotes) and use the function INDIRECT (INDIRECT), which converts a text link into a real, live one.
It remains only to click on OK. If we now add new elements to our table, they will be automatically included in it, which means they will be added to our drop-down list. Deletion is the same.
If you are too lazy to mess around with entering the INDIRECT formula, then you can simplify the process a little. After creating a smart table, simply select the range with the items for the drop-down list (A2:A5) with the mouse and enter a name for this range (without spaces) in the address field, for example intern, and click on Enter:
In fact, with this we create a named dynamic range that refers to the data from our smart table. Now the name of this range can be entered in the drop-down list creation window in the field Source:
Method 2: If you have Excel 2003 or older
Older versions of Excel before 2007 didn’t have the great smart spreadsheets, so you’ll have to mimic them yourself. This can be done with a named range and a function OFFSET, which is able to issue a link to a dynamic range of a given size.
Open the menu Insert – Name – Define or press Ctrl + F3. In the window that opens, click the button Add (New), enter a name for the range (any but without spaces and starting with a letter, for example − People) and in the field Reference enter the following formula:
=СМЕЩ(A2;0;0;СЧЁТЗ(A2:A100);1)
=OFFSET(A2;0;0;COUNTA(A2:A100);1)
Function (COUNTA) counts the number of non-empty cells in the last name column, i.e. the number of rows in the range for the dropdown list. Function OFFSET forms a link to a range with the names we need and uses the following arguments:
- A2 – starting cell
- 0 – shift the initial cell vertically down by a given number of lines
- 0 – shift the initial cell horizontally to the right by a given number of columns
- SCHÖTZ(A2:A100) – the size of the resulting range in the vertical direction, i.e. as many rows as we have occupied cells in the list
- 1 is the size of the horizontal range obtained at the output, i.e. one column
Now select the cells where you want to create drop down lists and select in older versions of excel from the menu Data – Check (Data – Validation). In the window that opens, on the tab Settings select option List and enter in the field Source here is the formula:
=People
After clicking on OK your dynamic list in selected cells is ready to go.
- 4 Ways to Create Dropdown Lists in Excel Sheet Cells
- Related dropdowns
- Quickly create dropdown lists with the PLEX add-on