Las hojas de cálculo de Excel^(Excel) a menudo incluyen menús desplegables de celdas para simplificar y/o estandarizar la entrada de datos. Estos menús desplegables se crean mediante la función de validación de datos para especificar una lista de entradas permitidas.

Para configurar una lista desplegable simple, seleccione la celda donde se ingresarán los datos, luego haga clic en Validación de datos^{(Data Validation)} (en la pestaña Datos ), seleccione ^(Data)Validación de datos^{(Data Validation)} , elija Lista^(List) (en Permitir^(Allow) :) y luego ingrese los elementos de la lista (separados por comas). ) en el campo Fuente^(Source) : (ver Figura 1).

En este tipo de menú desplegable básico, la lista de entradas permitidas se especifica dentro de la propia validación de datos; por lo tanto, para realizar cambios en la lista, el usuario debe abrir y editar la validación de datos. Sin embargo, esto puede ser difícil para usuarios sin experiencia o en casos en los que la lista de opciones es larga.

Otra opción es colocar la lista en un rango con nombre dentro de la hoja^{(named range within the spreadsheet)} de cálculo y luego especificar ese nombre de rango (precedido por un signo igual) en el campo Fuente^(Source) : de la validación de datos (como se muestra en la Figura 2^{(Figure 2)} ).

Este segundo método facilita la edición de las opciones en la lista, pero agregar o eliminar elementos puede ser problemático. Dado que el rango con nombre ( FruitChoices , en nuestro ejemplo) se refiere a un rango fijo de celdas ($H$3:$H$10 como se muestra), si se agregan más opciones a las celdas H11 o inferiores, no aparecerán en el menú desplegable (ya que esas celdas no forman parte de la gama FruitChoices ).

Del mismo modo, si, por ejemplo, las entradas Peras^(Pears) y Fresas^{(Strawberries)} se borran, ya no aparecerán en el menú desplegable, sino que el menú desplegable incluirá dos opciones "vacías" ya que el menú desplegable todavía hace referencia a todo el rango de FruitChoices, incluidas las celdas vacías H9 y H10 .

Por estos motivos, cuando se usa un rango con nombre normal como fuente de lista para un menú desplegable, el rango con nombre en sí debe editarse para incluir más o menos celdas si se agregan o eliminan entradas de la lista.

Una solución a este problema es usar un nombre de rango dinámico como fuente para las opciones desplegables. ^(dynamic)Un nombre de rango dinámico es uno que se expande (o contrae) automáticamente para coincidir exactamente con el tamaño de un bloque de datos a medida que se agregan o eliminan entradas. Para hacer esto, usa una fórmula^(formula) , en lugar de un rango fijo de direcciones de celda, para definir el rango con nombre.

Cómo configurar un rango dinámico^{(Dynamic Range)} en Excel

Un nombre de rango normal (estático) se refiere a un rango específico de celdas ($H$3:$H$10 en nuestro ejemplo, ver más abajo):

Pero un rango dinámico se define usando una fórmula (ver más abajo, tomada de una hoja de cálculo separada que usa nombres de rango dinámico):

Antes de comenzar, asegúrese de descargar nuestro archivo de ejemplo de Excel  (las macros de clasificación se han deshabilitado).

Examinemos esta fórmula en detalle. Las opciones para Frutas están en un bloque de celdas directamente debajo de un encabezado ( FRUTAS^(FRUITS) ). A ese encabezado también se le asigna un nombre: FruitsHeading :

La fórmula completa utilizada para definir el rango dinámico para las opciones de Frutas es:^(Fruits)

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

FruitsHeading se refiere al encabezado que está una fila arriba de la primera entrada en la lista. El número 20 (usado dos veces en la fórmula) es el tamaño máximo (número de filas) para la lista (esto se puede ajustar como se desee).

Tenga en cuenta que en este ejemplo, solo hay 8 entradas en la lista, pero también hay celdas vacías debajo de estas donde se pueden agregar entradas adicionales. El número 20 se refiere a todo el bloque donde se pueden realizar entradas, no al número real de entradas.

Ahora dividamos la fórmula en partes (codificando con colores cada parte) para entender cómo funciona:

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

La pieza "más interna" es OFFSET(FruitsHeading,1,0,20,1) . Esto hace referencia al bloque de 20 celdas (debajo de la celda FruitsHeading ) donde se pueden ingresar opciones. Esta función de DESPLAZAMIENTO^(OFFSET) básicamente dice: Comience en la celda FruitsHeading , baje 1 fila y sobre 0 columnas, luego seleccione un área que tenga 20 filas de largo y 1 columna de ancho. Eso nos da el bloque de 20 filas donde se ingresan las opciones de Frutas .^(Fruits)

La siguiente pieza de la fórmula es la función ISBLANK :

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

Aquí, la función OFFSET (explicada anteriormente) ha sido reemplazada por "lo anterior" (para facilitar la lectura). Pero la función ISBLANK está operando en el rango de celdas de 20 filas que define la función OFFSET .

ISBLANK luego crea un conjunto de 20 valores VERDADERO^(TRUE) y FALSO^(FALSE) , lo que indica si cada una de las celdas individuales en el rango de 20 filas al que hace referencia la función OFFSET está en blanco (vacío) o no. En este ejemplo, los primeros 8 valores del conjunto serán FALSO^(FALSE) ya que las primeras 8 celdas no están vacías y los últimos 12 valores serán VERDADERO^(TRUE) .

La siguiente pieza de la fórmula es la función ÍNDICE^(INDEX) :

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

Nuevamente, "lo anterior" se refiere a las funciones ISBLANK y OFFSET descritas anteriormente. La función ÍNDICE^(INDEX) devuelve una matriz que contiene los 20 valores TRUE / FALSE creados por la función ESBLANCO^(ISBLANK) .

ÍNDICE^(INDEX) se usa normalmente para elegir un determinado valor (o rango de valores) de un bloque de datos, especificando una determinada fila y columna (dentro de ese bloque). Pero establecer las entradas de fila y columna en cero (como se hace aquí) hace que ÍNDICE^(INDEX) devuelva una matriz que contiene todo el bloque de datos.

La siguiente pieza de la fórmula es la función MATCH :

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

La función COINCIDIR^(MATCH) devuelve la posición del primer valor VERDADERO^(TRUE) , dentro de la matriz que devuelve la función ÍNDICE^(INDEX) . Dado que las primeras 8 entradas de la lista no están en blanco, los primeros 8 valores de la matriz serán FALSO^(FALSE) y el noveno valor será VERDADERO^(TRUE) (ya que la novena ^fila del rango está vacía).

Entonces la función MATCH devolverá el valor de 9 . En este caso, sin embargo, realmente queremos saber cuántas entradas hay en la lista, por lo que la fórmula resta 1 del valor COINCIDIR^(MATCH) (que da la posición de la última entrada). Entonces, en última instancia, COINCIDIR^(MATCH) ( VERDADERO^(TRUE) , lo anterior, 0) -1 devuelve el valor de 8 .

La siguiente pieza de la fórmula es la función IFERROR :

=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)

La función IFERROR devuelve un valor alternativo, si el primer valor especificado da como resultado un error. Esta función se incluye porque, si todo el bloque de celdas (las 20 filas) están llenos de entradas, la función COINCIDIR^(MATCH) devolverá un error.

Esto se debe a que le estamos diciendo a la función COINCIDIR^(MATCH) que busque el primer valor VERDADERO^(TRUE) (en la matriz de valores de la función ESBLANCO^(ISBLANK) ), pero si NINGUNA^(NONE) de las celdas está vacía, toda la matriz se llenará con valores FALSO . ^(FALSE)Si MATCH no puede encontrar el valor de destino ( VERDADERO^(TRUE) ) en la matriz que está buscando, devuelve un error.

Entonces, si toda la lista está llena (y por lo tanto, COINCIDIR^(MATCH) devuelve un error), la función SI.ERROR^(IFERROR) devolverá el valor de 20 (sabiendo que debe haber 20 entradas en la lista).

Finalmente, OFFSET(FruitsHeading,1,0,the above,1) devuelve el rango que realmente estamos buscando: Comience en la celda FruitsHeading , baje 1 fila y más de 0 columnas, luego seleccione un área que tenga tantas filas como hay entradas en la lista (y 1 columna de ancho). Entonces, la fórmula completa devolverá el rango que contiene solo las entradas reales (hasta la primera celda vacía).

El uso de esta fórmula para definir el rango que es la fuente del menú desplegable significa que puede editar libremente la lista (agregar o eliminar entradas, siempre que las entradas restantes comiencen en la celda superior y sean contiguas) y el menú desplegable siempre reflejará el actual lista (ver Figura 6^{(Figure 6)} ).

El archivo de ejemplo (Listas dinámicas) que se ha utilizado aquí está incluido y se puede descargar desde este sitio web. Sin embargo, las macros no funcionan porque a WordPress no le gustan los libros de Excel con macros.

Como alternativa a especificar el número de filas en el bloque de lista, se puede asignar al bloque de lista su propio nombre de rango, que luego se puede usar en una fórmula modificada. En el archivo de ejemplo, una segunda lista ( Nombres^(Names) ) usa este método. Aquí, al bloque de lista completo (debajo del encabezado "NOMBRES", 40 filas en el archivo de ejemplo) se le asigna el nombre de rango de NameBlock . La fórmula alternativa para definir NamesList es entonces:

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

donde NamesBlock reemplaza OFFSET ( FruitsHeading,1,0,20,1 ) y ROWS(NamesBlock) reemplaza el 20 (número de filas) en la fórmula anterior.

Por lo tanto, para las listas desplegables que se pueden editar fácilmente (incluso por otros usuarios que pueden no tener experiencia), intente usar nombres de rango dinámico. Y tenga en cuenta que, aunque este artículo se ha centrado en las listas desplegables, los nombres de los rangos dinámicos se pueden usar en cualquier lugar donde necesite hacer referencia a un rango o una lista que puede variar en tamaño. ¡Disfrutar!

Use Dynamic Range Names in Excel for Flexible Dropdowns

Excel spreadsheetѕ often inclυdе cell dropdоwns to simрlify and/or standardize data entrу.  These dropdowns are created using the data validatіon feature to speсify a list of allоwable entries.

To set up a simple dropdown list, select the cell where data will be entered, then click Data Validation (on the Data tab), select Data Validation, choose List (under Allow:), and then enter the list items (separated by commas) in the Source: field (see Figure 1).

In this type of basic dropdown, the list of allowable entries is specified within the data validation itself; therefore, to make changes to the list, the user must open and edit the data validation.  This may be difficult, however, for inexperienced users, or in cases where the list of choices is lengthy.

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

This second method makes it easier to edit 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 range of cells ($H$3:$H$10 as shown), if more choices are added to the cells H11 or below, they will not show up in the dropdown (since those cells are not part of the FruitChoices range).

Likewise if, for example, the Pears and Strawberries entries are erased, they will no longer appear in the dropdown, but instead the dropdown will include two “empty” choices since the dropdown still references the entire FruitChoices range, including the empty cells H9 and H10.

For these reasons, when using a normal named range as the list source for a dropdown, the named range itself must be edited to include more or fewer cells if entries are added or deleted from the list.

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

How to Setup a Dynamic Range in Excel

A normal (static) range name refers to a specified range of cells ($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 which uses dynamic range names):

Before we get started, make sure you download our Excel example file (sort macros have been disabled).

Let’s examine this formula in detail.  The choices for Fruits are in a block of cells directly below a heading (FRUITS).  That heading is also assigned a name: FruitsHeading:

The entire formula used to define the dynamic range for the Fruits choices is:

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

FruitsHeading refers to the heading that is one row above the first entry in the list.  The number 20 (used two times in the formula) is the maximum size (number of rows) for the list (this can be adjusted as desired).

Note that in this example, there are only 8 entries in the list, but there are also empty cells below these where additional entries could be added.  The number 20 refers to the entire block where entries can be made, not to the actual number of entries.

Now let’s break down the formula into pieces (color-coding each piece), to understand how it works:

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

The “innermost” piece is OFFSET(FruitsHeading,1,0,20,1).  This references the block of 20 cells (underneath the FruitsHeading cell) where choices may be entered.  This OFFSET function basically says: Start at the FruitsHeading cell, go down 1 row and over 0 columns, then select an area that is 20 rows long and 1 column wide.  So that gives us the 20-row block where the Fruits choices are entered.

The next piece of the formula is the ISBLANK function:

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

Here, the OFFSET function (explained above) has been replaced with “the above” (to make things easier to read).  But the ISBLANK function is operating on the 20-row range of cells that the OFFSET function defines.

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 OFFSET function is blank (empty) 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,INDEX(the above,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.

INDEX is normally used to pick a certain value (or range of values) out of a block of data, by specifying a certain row and column (within that block).  But setting the row and column inputs to zero (as is done here) causes INDEX to return 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,the above,0)-1,20),1)

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

So 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 ultimately, MATCH(TRUE,the above,0)-1 returns the value of 8.

The next piece of the formula is the IFERROR function:

=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)

The IFERROR function returns an alternate value, if the first value specified results in an error.  This function is included since, if the entire block of cells (all 20 rows) are filled with entries, the MATCH function will return an error.

This is because we’re 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 entire array will be filled with FALSE values.  If MATCH cannot find the target value (TRUE) in array it is searching, it returns an error.

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

Finally, OFFSET(FruitsHeading,1,0,the above,1) returns the range we are actually looking for:  Start at the FruitsHeading cell, go down 1 row and over 0 columns, then select an area that is however many rows long as there are entries in the list (and 1 column wide).  So the entire formula together will return the range that contains only the actual entries (down to the first empty cell).

Using this formula to define the range that is the source for the dropdown means you can freely edit the list (adding or removing entries, as long as the remaining entries start at the top cell and are contiguous) and the dropdown will always reflect the current list (see Figure 6).

The example file (Dynamic Lists) that’s been used here is included and is downloadable from this website. The macros don’t work, however, because WordPress doesn’t like Excel books with macros in 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 example file, a second list (Names) uses this method.  Here, the entire list block (underneath the “NAMES” heading, 40 rows in the example file) is assigned the range name of NameBlock.  The alternate formula for defining the NamesList is then:

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

where NamesBlock replaces OFFSET(FruitsHeading,1,0,20,1) and ROWS(NamesBlock) replaces the 20 (number of rows) in the earlier formula.

So, for dropdown lists which can be easily edited (including by other users who may be inexperienced), try using dynamic range names!  And note that, although this article has been focused on dropdown lists, dynamic range names can be used anywhere you need to reference a range or list that can vary in size. Enjoy!

Related posts

• Cómo eliminar líneas en blanco en Excel

• Cómo usar la función Hablar celdas de Excel

• Cómo insertar una hoja de cálculo de Excel en un documento de Word

• Cómo usar el análisis What-If de Excel

• Cómo arreglar una fila en Excel

• Cómo eliminar filas duplicadas en Excel

• 3 formas de dividir una celda en Excel

• Use Spike para cortar y pegar varios elementos de texto en Word

• 4 formas de usar una marca de verificación en Excel

• Cómo ordenar por fecha en Excel

• Cómo usar referencias absolutas en Excel

• 2 formas de usar la función de transposición de Excel

• Diferencias entre Microsoft Excel Online y Excel para escritorio

• Cómo insertar rápidamente varias filas en Excel

• Cómo mover columnas en Excel

• Centre los datos de su hoja de trabajo en Excel para imprimir

• 5 formas de convertir texto a números en Excel

• Uso de la herramienta de búsqueda de objetivos de análisis hipotético de Excel

• Hojas de cálculo de Google vs Microsoft Excel: ¿cuáles son las diferencias?

• Cuándo usar Index-Match en lugar de VLOOKUP en Excel