contador Saltar al contenido

Use dynamic range names in Excel for flexible drop-down lists

Excel spreadsheets often include cell drop-down menus to simplify and / or standardize data entry. These drop-down lists are created using the data validation feature to specify a list of allowed entries.

To set up a simple drop-down list, select the cell where the data will be placed, then click Validate data (in the form Data ), select Validate Data, select List (in Allow :), then enter the items in the list (separated by commas) in the field Source : (see Figure 1).

In this type of basic drop-down menu, the list of allowed items specified within the validation of the data itself; therefore, to make changes to the list, the user must open and edit the data validation. This can be difficult, however, for novice users, or in cases where the list of choices is long.

Another option is to place the list in a named range within the spreadsheet and then specify the name of the range (preceded by an equal sign) in the field Origin : data validation (as shown in Figure 2).

This second method simplifies editing the choices in the list, but adding or removing items can be problematic. Since the named range (FruitChoices, in our example) refers to a fixed cell range ($ H $ 3: $ H $ 10 as shown), if additional options are added to cells H11 or lower, they will not be displayed in the menu. drop down (since these cells are not part of the FruitChoices range).

Likewise, if, for example, the Pears and Strawberry entries are cleared, they will no longer appear in the drop-down menu, but the drop-down menu will include two "empty" choices since the drop-down menu still refers to the entire FruitChoices range, including empty cells H9 and H10.

For these reasons, when using a normal named range as the list source for a drop-down menu, the named range must be changed to include more or less cells if the items are added or removed from the list.

One solution to this problem is to use a range name dynamic as the source for the drop-down menu choices. A dynamic range name one that automatically expands (or contracts) exactly to match the size of a block of data when entries are added or removed. To do this, we use one formula, rather than a fixed range of cell addresses, to define the named range.

How to set up a dynamic range in Excel

A normal (static) range name refers to a specified cell range ($ H $ 3: $ H $ 10 in our example, see below):

But a dynamic range is defined using a formula (see below, taken from a separate spreadsheet that uses dynamic range names):

Before you start, be sure to download our Excel sample file (sort macros have been disabled).

Let's examine this formula in detail. The choices for the fruits are in a block of cells directly under a title ( FRUIT ). TO that header a name is also assigned: FruitsHeading :

The whole formula used to define the dynamic range for fruit choices:

 = OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX (ISBLANK (OFFSET (FruitsHeading, 1, 0, 20, 1)), 0, 0), 0) -1, 20), 1) 

FruitsHeading yes refers to the header that a row above the first entry in the list. The number 20 (used twice in the formula) is the maximum size (number of lines) for the list (this can be adjusted as desired).

Note that in this example there are only 8 items in the list, but there are also empty cells below them where you can add more items. The number 20 refers to the entire block in which the entries can be placed, not to the actual number of entries.

Now let's divide the formula into pieces (color coding each piece) to understand how it works:

 = OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDICE (ISBLANK ( OFFSET (FruitsHeading, 1, 0, 20, 1) ), 0, 0), 0) -1, 20), 1) 

The "innermost" piece OFFSET (FruitsHeading, 1, 0, 20, 1) . This refers to the block of 20 cells (under the FruitsHeading cell) in which you can insert your choices. This OFFSET function basically says: Start in the cell FruitsHeading, go to 1 row and 0 columns, then select an area 20 rows long and 1 column wide. So that gives us the block of 20 rows in which the fruit choices are placed.

The next piece of the formula is the function ISBLANK :

 = OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDICE ( ISBLANK (sopra), 0, 0), 0) -1, 20), 1) 

Here, the OFFSET function (explained above) has been replaced with "the previous one" (to make things easier to read). But the ISBLANK function works on a 20-row cell range defined by the OFFSET function.

ISBLANK then creates a set of 20 TRUE and FALSE values, indicating whether each of the individual cells in the 20-row range referenced by the empty (empty) OFFSET function or not. In this example, the first 8 values ??in the set will be FALSE since the first 8 cells are not empty and the last 12 values ??will be TRUE.

The next piece of the formula is the INDEX function:

 = OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDICE (sopra, 0, 0), 0) -1, 20), 1) 

Again, "the above" refers to the ISBLANK and OFFSET functions described above. The INDEX function returns an array containing the 20 TRUE / FALSE values ??created by the ISBLANK function.

THE INDEX it is normally used to select a specific value (or range of values) from a block of data, specifying a specific row and column (within that block). But by setting the row and column inputs to zero (as is done here), INDEX returns an array containing the entire block of data.

The next piece of the formula is the MATCH function:

 = OFFSET (FruitsHeading, 1, 0, IFERROR ( MATCH (TRUE, sopra, 0) -1, 20), 1) 

The function MATCH returns the position of the first TRUE value, within the array returned by the INDEX function. Since the first 8 entries in the list are not empty, the first 8 values ??in the array will be FALSE and the ninth value will be TRUE (since the 9th row in the empty range).

Then the MATCH function will return the value of 9 . In this case, however, we really want to know how many entries are in the list, so the formula subtracts 1 from the MATCH value (which gives the position of the last entry). So in the end, MATCH (TRUE, the previous one, 0) -1 returns the value of 8 .

The next piece of the formula is the IFERROR function:

 = OFFSET (FruitsHeading, 1, 0, IFERROR (sopra, 20), 1) 

The IFERROR function returns an alternate value if the first specified value causes an error. This function included because if the entire block of cells (all 20 rows) full of entries, the MATCH function will return an error.

This is because we are telling the MATCH function to look for the first TRUE value (in the array of values ??from the ISBLANK function), but if NONE of the cells are empty, then the whole array will be filled with FALSE values. If MATCH cannot find the target value (TRUE) in the matrix it is looking for, it returns an error.

So if the entire list is full (and therefore MATCH returns an error), the IFERROR function will return the value of 20 instead (knowing that there must be 20 entries in the list).

Finally, OFFSET (FruitsHeading, 1, 0, the previous one, 1) returns the range that we are actually looking for: Start from the FruitsHeading cell, go down 1 row and go over 0 columns, then select an area that still has many long lines as there are items in the list (and 1 column wide). Then the whole formula together will return the range containing only the actual entries (up to the first empty cell).

Using this formula to define the range that represents the origin of the drop-down list means that you can freely edit the list (adding or removing entries, as long as the remaining entries start from the top cell and are contiguous) and the menu drop-down will always reflect the current list (see Figure 6).

The sample file (dynamic lists) that was used here included and downloadable from this website. Macros don't work, however, because WordPress doesn't like Excel books with macros inside them.

As an alternative to specifying the number of rows in the list block, the list block can be assigned its own range name, which can then be used in a modified formula. In the sample file, a second list (names) uses this method. Here, the entire list block (under the heading "NAMES", 40 lines in the example file) is given the name of the range of NameBlock . The alternative formula for the definition of NamesList therefore:

 = OFFSET (NamesHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX (ISBLANK ( NamesBlock ), 0, 0), 0) -1, ROWS (NamesBlock) ), 1) 

where is it NamesBlock replaces OFFSET (FruitsHeading, 1, 0, 20, 1) e ROWS (NamesBlock) replaces 20 (number of lines) in the previous formula.

So, for drop-down lists that can be easily edited (even by other users who may be inexperienced), try using dynamic range names! And note that although this article has focused on drop-down lists, dynamic range names can be used wherever you need to refer to a range or list that can vary in size. To enjoy!