Lista de Nombres de Hojas con Fórmula – Excel y Google Sheets

Este tutorial demuestra cómo listar los nombres de las hojas de un libro con una fórmula en Excel.

 

Listar los nombres de las hojas utilizando un rango con nombre y una fórmula

No hay una función incorporada en Excel que pueda listar todas las hojas de un libro. En su lugar, tiene dos opciones:

1. Utilizar una macro VBA para listar todas las hojas del libro de trabajo
2. Crear una fórmula para listar todas las hojas

Si desea utilizar una fórmula, siga estos pasos:

1. Crear un rango con nombre «Hojas de trabajo»
2. Utilice una fórmula para listar todos los nombres de las hojas.

Crear un Rango de Nombres para los nombres de las hojas

Para crear un Rango de Nombres para los nombres de las hojas, en la cinta de Excel Fórmulas > Administrador de nombres > Nuevo

 

Escriba «Worksheets» en el cuadro de nombre:

 

En la sección «Se refiere a» del cuadro de diálogo, tendremos que escribir la fórmula

=GET.WORKBOOK(1) & T(NOW())

Esta fórmula almacena los nombres de todas las hojas (como una matriz con este formato: «[Worksheet.xlsm].Resumen») del libro de trabajo en el rango con nombre «Worksheets».

La función «GET.WORKBOOK» es una función de macro, por lo que su libro de trabajo tiene que ser guardado como un libro de trabajo habilitado para macro (formato de archivo: .xlsm) para que los nombres de las hojas se actualicen cada vez que se abra el libro de trabajo.

Nota: Al rellenar el cuadro de diálogo Editar nombre, debe seleccionarse el libro de trabajo como ámbito del rango de nombres.

 

Uso de la fórmula para listar los nombres de las hojas

Ahora utilizaremos una fórmula para listar los nombres de las hojas. Necesitaremos las funciones INDICE, EXTRAE, ENCONTRAR y FILAS:

=INDICE(EXTRAE(Worksheets;ENCONTRAR("]";Worksheets)+1;255);FILAS($B$5:B6))

 

 

• La fórmula anterior toma el rango «Worksheets» y muestra cada nombre de hoja basado en su posición.
• Las funciones EXTRAE y ENCONTRAR extraen los nombres de las hojas de la matriz (eliminando el nombre del libro de trabajo).
• Luego, las funciones INDICE y FILAS muestran cada valor en esa matriz.
• Aquí, «Resumen» es la primera hoja de los libros de trabajo y «Limpia» es la última.

Haga clic en el enlace para obtener más información sobre cómo las funciones EXTRAE y ENCONTRAR obtienen los nombres de las Hojas.

 

Método alternativo

También tiene la opción de crear la lista de nombres de hoja («SheetNames») dentro del Administrador de nombres. En lugar de

=GET.WORKBOOK(1) & T(NOW())

establezca su campo «Refiere a» como

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

Ahora no hay necesidad de EXTRAE, ENCONTRAR, y FILAS en su fórmula.

Tu rango con nombre ya está compuesto sólo por nombres de hojas. Usa esta fórmula INDICE más sencilla para listar las hojas:

=INDICE(SheetNames;B3)