Lista de hojas de trabajo en un menú desplegable

En esta publicación, responderé una pregunta que recibí de Sara sobre cómo crear un menú desplegable con el nombre de cada hoja de trabajo y hacer referencia a él en una fórmula para recuperar valores de la hoja seleccionada. Como se trata de Excel, existen diversas alternativas para lograrlo. En esta publicación, demostraré una de esas soluciones que utiliza un nombre definido, validación de datos y la función INDIRECTA. Además, como beneficio adicional, demostraré cómo podemos generar dinámicamente la lista de nombres de hojas usando Power Query. ¡Gracias Sara por tu pregunta!

Video

Objetivo

Primero, seamos claros acerca de lo que estamos tratando de lograr. Tenemos un libro de trabajo con varias hojas… quizás una hoja para cada departamento como esta:

Cada hoja tiene la misma estructura de datos básica, como esta:

En una hoja de resumen, queremos crear un menú desplegable para que un usuario pueda elegir una de las hojas de departamento, como este:

Y cuando se selecciona una hoja, queremos usar el nombre de la hoja seleccionada en una fórmula para hacer referencia a los valores en la hoja seleccionada, como esto:

Vamos a por ello.

Tutorial

En general, aquí está el plan de juego:

• Paso 1: crea una tabla con los nombres de las hojas
• Paso 2: cree un nombre definido que haga referencia al nombre de la tabla
• Paso 3: crea un menú desplegable con validación de datos
• Paso 4: use la función INDIRECTA para crear una referencia a la hoja seleccionada
• Paso adicional: opcionalmente, use Power Query para crear la lista de nombres de hojas de forma dinámica

Paso 1: crear una tabla con los nombres de las hojas

Comencemos creando una tabla con los nombres de las hojas. Si solo hay unas pocas hojas, lo más fácil es crear la tabla manualmente. Si tiene un montón de hojas, asegúrese de consultar el paso adicional final que muestra cómo usar Power Query para crear la lista de nombres de hojas de forma dinámica.

Comience escribiendo los nombres de las hojas en algunas celdas, como esta:

Luego, convierta ese rango ordinario en una tabla seleccionándolo y seleccionando el comando Insertar Tabla . La tabla resultante debería verse así:

Si esta es la primera tabla del libro de trabajo, probablemente tenga el nombre Tabla1. Puede ver o editar el nombre de la tabla como desee utilizando el campo Diseño de tabla Nombre de tabla .

Paso 2: crea un nombre definido

A continuación, queremos crear un nombre definido que podamos usar en el siguiente paso. Para hacerlo, simplemente haga clic en Fórmulas Administrador de nombres .

En el cuadro de diálogo Administrador de nombres resultante , haga clic en Nuevo y luego cree un nuevo nombre como ListaHoja y configúrelo igual al nombre de la tabla en el paso anterior (por ejemplo, =Tabla1 ), así:

Nota: se requiere el signo igual = delante del nombre de la tabla.

Paso 3: crea un menú desplegable

Ahora podemos crear el menú desplegable. Seleccione la celda para su menú desplegable y seleccione Datos Validación de datos . En el cuadro de diálogo Validación de datos resultante, desea Permitir una Lista y la Fuente es =HojaLista (o cualquier nombre que haya definido en el paso anterior), así:

Nota: se requiere el signo igual = delante del nombre de SheetList.

Cuando haces clic en Aceptar, deberías tener un menú desplegable funcional, como este:

A continuación, queremos hacer referencia al nombre de la hoja seleccionada en nuestra fórmula. Para hacer eso, usaremos la función INDIRECTA.

Paso 4: use INDIRECTO

La función INDIRECTA convierte un valor de texto en una referencia real de Excel.

Empecemos con un ejemplo sencillo de referencia de celda. Podríamos pasar una cadena de texto “A1” a la función INDIRECTA y devolvería una referencia real a la celda A1. Esta fórmula se vería así:

=INDIRECTO("A1")

Demos el siguiente paso y descubramos cómo hacer referencia a una celda en otra hoja. Si escribimos un signo igual = en cualquier celda y luego hacemos clic en una celda en una hoja de trabajo diferente, podemos inspeccionar la referencia resultante para ver cómo Excel hace esto.

Por ejemplo, la referencia a la celda A1 en una hoja denominada Departamento A tiene este aspecto:

='Dpto A'!A1

Dos cosas a tener en cuenta:

• Primero, usamos un signo de exclamación. entre el nombre de la hoja y la referencia de la celda
• En segundo lugar, encerramos el nombre de la hoja entre comillas simples.

Entonces, juntemos todo esto.

El nombre de la hoja deseada se selecciona en un menú desplegable. Digamos que el menú desplegable está en la celda C5, así:

Y digamos que queremos recuperar el Administrador de la hoja seleccionada, que está almacenada en B4, así:

Luego escribiríamos una fórmula usando INDIRECTO, como esta:

=INDIRECTO( " ' "    C5    " '!B4 " )

Nota: agregué muchos espacios adicionales y algunos subrayados para intentar que sea más fácil de leer. Estos espacios deben eliminarse de su fórmula real.

Entendamos lo que está pasando. Primero, usamos INDIRECTO para convertir un valor de texto en una referencia real de Excel.

El valor de texto incluye tres componentes, todos unidos con el operador de concatenación :

• La comilla simple de apertura (entre comillas) para el nombre de la hoja.
• La referencia a la celda desplegable (que tiene el nombre de la hoja)
• La comilla simple de cierre para el nombre de la hoja, luego el signo de exclamación para separar el nombre de la hoja de la referencia de celda y, finalmente, la referencia de celda deseada (entre comillas)

Cuando presionamos Enter, la fórmula recupera el valor de B4 de la hoja seleccionada en el menú desplegable… ¡yay!

¿Qué pasaría si quisiéramos sumar una columna completa de la hoja seleccionada? No hay problema. Básicamente, podemos usar la función INDIRECTA en fórmulas donde usaríamos una celda normal o una referencia de rango.

Entonces, para sumar toda la columna C en la hoja seleccionada, escribimos una fórmula como esta:

=SUM( INDIRECTO("'"  C5  "'!C:C") )

Nota: agregué espacios adicionales para intentar que la fórmula sea más fácil de leer.

Paso adicional: utilice Power Query para crear una lista de hojas dinámicas

Cuando sólo hay un puñado de nombres de hojas, escribirlos manualmente es lo más rápido y sencillo. Pero, ¿qué sucede si tiene un montón de nombres de hojas o agrega o elimina hojas con frecuencia del libro de trabajo? En este caso, podemos hacer que Power Query genere la lista de nombres de hojas por nosotros.

Una forma de hacerlo es hacer clic en Obtener datos Desde archivo Desde libro de trabajo en el grupo de comandos Obtener y transformar. A continuación, navegamos hasta el libro de trabajo. En el cuadro de diálogo Navegador resultante, simplemente seleccionamos cualquier hoja (no importa cuál en este momento porque la cambiaremos más adelante).

A continuación hacemos clic en el botón Transformar datos para abrir el Editor de Power Query.

Usamos la X para eliminar todos los pasos aplicados excepto el paso Fuente. Entonces, al principio la lista de Pasos Aplicados puede verse así:

Luego, haga clic en la X para eliminar todo excepto el paso Fuente:

En este punto, verá una lista de las hojas, tablas y nombres definidos que se encuentran en el libro de trabajo. Estos se identifican en la columna Tipo:

Como solo queremos devolver una lista de Hojas, filtramos la columna Tipo para mantener solo las filas de Hojas.

Nota: incluso si solo tiene filas de Hoja, aún desea aplicar un Filtro de texto = Hoja para que se excluyan las nuevas tablas o nombres que se creen más adelante.

En este punto, deberíamos ver una lista de las hojas de trabajo en la columna Nombre:

Como queremos excluir la hoja Resumen del menú desplegable, simplemente usaremos un filtro para excluirla.

Además, solo queremos devolver la columna Nombre a Excel, por lo que hacemos clic derecho en el encabezado de la columna Nombre y seleccionamos Eliminar otras columnas .

A continuación, hacemos clic en Cerrar y cargar en … y seleccionamos Tabla. Podemos insertar la tabla en una hoja nueva o existente según lo deseemos.

La tabla resultante debería verse así:

Ahora, para indicarle al menú desplegable que haga referencia a esta nueva tabla en lugar de a la tabla original (azul), volvemos al Administrador de nombres ( Fórmulas Administrador de nombres) . Editamos el nombre de SheetList y lo cambiamos para reflejar la tabla de resultados de Power Query (verde) . Puede determinar el nombre de la tabla haciendo clic en ella y mirando el campo Diseño de tabla Nombre de tabla .

Una vez hecho esto, podemos eliminar de forma segura la tabla original (azul) del libro.

En el futuro, cuando cambiemos los nombres de las hojas, agreguemos nuevas hojas o eliminemos hojas, podremos (1) guardar el libro y luego (2) hacer clic con el botón derecho en la tabla de resultados de Power Query (verde) y actualizar . Esto completará la tabla de resultados con las hojas actuales y el menú desplegable las reflejará. Sí… ¡lo logramos!

NOTA : deberá guardar el libro de trabajo para que se reflejen los cambios en los nombres de las hojas. En otras palabras, cuando actualiza la tabla, Power Query examina la versión guardada y excluye cualquier cambio que se haya realizado desde la última vez que guardó.

NOTA : si no filtró por tipo Hoja como se sugiere, su lista de nombres de hojas puede incluir nombres de tablas y nombres definidos a lo largo del tiempo a medida que se agregan, incluida la tabla de resultados. Si esto sucede, puede editar la consulta y aplicar un filtro para incluir solo Tipo=Hoja.

NOTA: si cambia el nombre o mueve la ubicación de este libro, querrá editar el paso Fuente de la consulta para hacer referencia al nuevo nombre o ubicación.

Conclusión

Si tiene alguna mejora u otro enfoque al respecto, compártala publicando un comentario a continuación… ¡gracias!

Archivo de muestra : SheetDropDown.xlsx