¿Cómo crear un índice de contenido para todas las hojas y mantenerlo actualizado automáticamente?
Imagínese que tiene un libro con cientos de hojas de cálculo. Encontrar una hoja específica entre tantas puede resultar complicado para la mayoría de nosotros. En estos casos, crear un índice de contenido le permite acceder rápida y fácilmente a la hoja que necesita. Este tutorial le muestra cómo crear un índice de contenido para todas sus hojas y mantenerlo actualizado automáticamente cada vez que inserte, elimine o cambie el nombre de una hoja.
Use código VBA para crear un índice de contenido para todas las hojas y actualizarlo automáticamente
Use una fórmula para crear un índice de contenido para todas las hojas y actualizarlo automáticamente
En Excel, puede crear un índice de contenidos definiendo un nombre de celda y haciendo referencia a ese nombre en una fórmula. Siga estos pasos:
1. Inserte una hoja nueva antes de todas las demás en la que desee crear el índice de contenido y renómbrela según sus necesidades.
2. A continuación, haga clic en «Fórmulas» > «Definir nombre», tal como se muestra en la captura de pantalla:
3. En el cuadro de diálogo «Nuevo nombre», especifique «Sheetlist» en el campo «Nombre» (o cualquier otro nombre de su preferencia) e introduzca la siguiente fórmula en el cuadro «Se refiere a».
=GET.WORKBOOK(1)&T(NOW())
4. A continuación, haga clic en el botón «Aceptar» para cerrar el cuadro de diálogo.
5. Ahora vaya a la hoja nueva en la que desea crear el índice de contenido e introduzca la siguiente fórmula en una celda vacía:
=IFERROR(INDEX(MID(Sheetlist,FIND("]",Sheetlist)+1,255),ROWS($A$2:A2)),"")6. A continuación, arrastre el controlador de relleno hacia abajo hasta que aparezcan celdas vacías. De este modo, se mostrarán todos los nombres de las hojas del libro de trabajo actual, incluidas las ocultas, tal como se muestra en la siguiente captura de pantalla:
7. A continuación, cree un hipervínculo para el índice de contenido utilizando la siguiente fórmula:
=HYPERLINK("#'"&A2&"'!A1","Go To Sheet")
8. Ahora, al hacer clic en el texto del hipervínculo, se desplazará inmediatamente a esa hoja. Además, si inserta una hoja nueva, elimina una hoja o cambia el nombre de una hoja, el índice de contenido se actualizará automáticamente.
- 1. Con este método, todas las hojas ocultas aparecen también en el índice del contenido.
- 2. Debe guardar el archivo en formato «Libro de Excel habilitado para macros» para que, la próxima vez que lo abra, las fórmulas funcionen correctamente.
Use Kutools para Excel para crear un índice de contenido para todas las hojas y actualizarlo automáticamente
Si dispone de «Kutools para Excel», su función «Navegación» le permite mostrar todos los nombres de las hojas en un panel lateral vertical y navegar rápida y fácilmente hasta la hoja deseada.
Después de instalar Kutools para Excel, proceda de la siguiente manera:
1. Haga clic en «Kutools» > «Navegación»; consulte la captura de pantalla:
2. En el panel expandido de «Navegación», haga clic en el icono «Libro y Hoja». En el cuadro de lista superior aparecerán todos los libros abiertos y, en el inferior, todas las hojas visibles del libro actual, tal como se muestra en la captura de pantalla:
3. A partir de ahora, podrá acceder directamente a cualquier hoja con solo hacer clic en su nombre desde el panel izquierdo. Además, al eliminar, insertar o renombrar una hoja, la lista del panel se actualizará automáticamente.
Use código VBA para crear un índice de contenido para todas las hojas y actualizarlo automáticamente
A veces no es necesario incluir las hojas ocultas en el índice de contenido. Para solucionar este inconveniente, el siguiente código VBA le resultará de gran utilidad.
1. Inserte una hoja nueva antes de todas las demás en la que desee crear el índice de contenido y renómbrela según sus necesidades. A continuación, haga clic con el botón derecho en la pestaña de la hoja y seleccione «Ver código» en el menú contextual; consulte la captura de pantalla:
2. En la ventana abierta de «Microsoft Visual Basic para Aplicaciones», copie y pegue el siguiente código en la ventana de código correspondiente a la hoja:
Código VBA: Crear un índice de contenido para todas las hojas y actualizarlo automáticamente
Private Sub Worksheet_Activate()
'Updateby ExtendOffice
Dim xWsh As Worksheet
Dim xWshs As Worksheets
Dim xShowHinddenWorkSheet As Boolean
Dim xI As Long
Dim xRg As Range
Dim xStrTitle, xStrTCHeader, xStrWShName As String
xShowHinddenWorkSheet = False 'Change this to True to display the hidden sheets as you need
xStrTitle = "A1"
xStrTCHeader = "A3"
On Error Resume Next
Application.ScreenUpdating = False
Me.Cells.Clear
Me.Range(xStrTitle).Font.Bold = True
Me.Range(xStrTitle).Font.Size = Me.Range(xStrTitle).Font.Size + 2
Me.Range(xStrTitle).Value = "Table of Contents"
Me.Range(xStrTCHeader).Value = "No."
Me.Range(xStrTCHeader).Offset(0, 1).Value = "Sheet Name"
Me.Range(xStrTCHeader).Resize(1, 2).Font.Bold = True
xStrWShName = Me.Name
xI = 1
For Each xWsh In Application.ActiveWorkbook.Worksheets
If xWsh.Name <> xStrWShName Then
If (xWsh.Visible = xlSheetVisible) Or xShowHinddenWorkSheet Then
Me.Hyperlinks.Add Anchor:=Me.Range(xStrTCHeader).Offset(xI, 1), Address:="", SubAddress:="'" & xWsh.Name & "'!A1", TextToDisplay:=xWsh.Name
Me.Range(xStrTCHeader).Offset(xI).Value = xI
xI = xI + 1
End If
End If
Next
Application.ScreenUpdating = True
End Sub
3. A continuación, pulse la tecla «F5» para ejecutar este código. El índice de contenido se generará al instante en la nueva hoja de cálculo, excluyendo las hojas ocultas, tal como se muestra en la siguiente captura de pantalla:
4. A partir de ahora, al eliminar, insertar o cambiar el nombre de una hoja, el índice de contenido se actualizará automáticamente.
Las mejores herramientas de productividad para Office
Potencie sus habilidades en Excel con Kutools para Excel y experimente una eficiencia como nunca antes.Kutools para Excel ofrece más de 300 funciones avanzadas para aumentar su productividad y Ahorrar tiempo.Haga clic aquí para obtener la función que más necesita...
Office Tab aporta una interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil
- Active la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abra y cree varios documentos en nuevas pestañas dentro de la misma ventana, en lugar de hacerlo en ventanas separadas.
- ¡Aumente su productividad en un 50 % y elimine cientos de clics del ratón cada día!
Todos los complementos de Kutools en un solo instalador.
Kutools for Office es la suite que incluye complementos para Excel, Word, Outlook y PowerPoint, además de Office Tab Pro, ideal para equipos que trabajan en distintas aplicaciones de Office.
- Suite integral— complementos para Excel, Word, Outlook y PowerPoint + Office Tab Pro
- Un instalador, una licencia— configuración en minutos (compatible con MSI)
- Rendimiento mejorado en conjunto— productividad optimizada en todas las aplicaciones de Office
- Prueba gratuita de 30 días con todas las funciones— sin registro ni tarjeta de crédito
- La mejor relación calidad-precio— ahorre frente a la compra individual de complementos