¿Cómo crear un índice para todas las hojas y que se actualice automáticamente?

Supongamos que tienes un libro de trabajo que contiene cientos de hojas de cálculo, navegar hasta una hoja específica entre múltiples hojas puede ser un dolor de cabeza para la mayoría de nosotros. En este caso, crear un índice para estas hojas de cálculo puede ayudarte a ir rápidamente y fácilmente a una hoja en particular. Este tutorial hablará sobre cómo crear un índice para todas las hojas y actualizar la tabla automáticamente cuando se inserten, eliminen o cambien los nombres de las hojas.

Usar fórmulas para crear un índice para todas las hojas y que se actualice automáticamente

Usar Kutools para Excel para crear un índice para todas las hojas y que se actualice automáticamente

Usar código VBA para crear un índice para todas las hojas y que se actualice automáticamente

Usar fórmulas para crear un índice para todas las hojas y que se actualice automáticamente

En Excel, puedes crear un índice definiendo un nombre de rango y haciendo referencia al nombre en una fórmula; sigue los siguientes pasos:

1. Inserta una nueva hoja antes de todas las hojas donde deseas crear el índice y cámbiale el nombre según sea necesario.

2. Luego, haz clic en "Fórmula" > "Definir nombre", ver captura de pantalla:

3. En el cuadro de diálogo "Nuevo nombre", especifica un nombre "Sheetlist" en el cuadro "Nombre", puedes cambiarlo por el tuyo propio, y luego introduce la siguiente fórmula en el cuadro de texto "Se refiere a":

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

4. Luego, haz clic en el botón "Aceptar" para cerrar el cuadro de diálogo.

5. Ahora, ve a la nueva hoja donde deseas crear un índice y luego introduce la siguiente fórmula en una celda en blanco:

=IFERROR(INDEX(MID(Sheetlist,FIND("]",Sheetlist)+1,255),ROWS($A$2:A2)),"")

6. Y luego arrastra el controlador de relleno hacia abajo hasta que aparezcan celdas en blanco, y todos los nombres de las hojas (incluidas las hojas ocultas) del libro de trabajo actual se habrán listado como se muestra en la siguiente captura de pantalla:

7. Luego, debes crear hipervínculos para el contenido del índice, utiliza la siguiente fórmula:

=HYPERLINK("#'"&A2&"'!A1","Go To Sheet")

8. Ahora, al hacer clic en el texto del hipervínculo, irá a esa hoja de inmediato. Y si insertas una nueva hoja, eliminas una hoja o cambias el nombre de la hoja, el contenido del índice se actualizará automáticamente.

Usar Kutools para Excel para crear un índice para todas las hojas y que se actualice automáticamente

Si tienes "Kutools para Excel", su "Panel de navegación" puede ayudarte a listar todos los nombres de las hojas en un panel izquierdo verticalmente, y navegar rápidamente y fácilmente a la hoja específica.

Después de instalar Kutools para Excel, por favor haz lo siguiente:

1. Haz clic en "Kutools" > "Navegación", ver captura de pantalla:

2. En el panel expandido "Navegación", haz clic en el icono "Libro de trabajo y hoja", todos los libros de trabajo abiertos están listados en el cuadro de lista superior, y todas las hojas visibles dentro del libro de trabajo se muestran en el cuadro de lista inferior, ver captura de pantalla:

3. Y ahora, puedes ir a la hoja simplemente haciendo clic en el nombre de la hoja desde el panel izquierdo. Y cuando elimines, insertes o renombres una hoja, la lista de hojas en el panel se actualizará dinámicamente.

Usar código VBA para crear un índice para todas las hojas y que se actualice automáticamente

A veces, no necesitas mostrar las hojas ocultas en el contenido del índice. Para resolver este problema, el siguiente código VBA puede ayudarte.

1. Inserta una nueva hoja antes de todas las hojas donde deseas crear el índice y cámbiale el nombre según sea necesario. Luego, haz clic derecho en la pestaña de la hoja y elige "Ver código" desde el menú contextual, ver captura de pantalla:

2. En la ventana abierta "Microsoft Visual Basic para Aplicaciones", copia y pega el siguiente código en la ventana de código de la hoja:

Código VBA: Crear un índice para todas las hojas y que se actualice 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. Luego, presiona la tecla "F5" para ejecutar este código, y el contenido del índice se crea de inmediato en la nueva hoja de trabajo, todas las hojas ocultas no se mostrarán en el contenido del índice, ver captura de pantalla:

4. A partir de ahora, al eliminar, insertar o renombrar una hoja, el contenido del índice se actualizará dinámicamente.