Referencia dinámica de hoja de trabajo o libro en Excel

Supongamos que tienes datos con el mismo formato en varias hojas de trabajo o libros y necesitas obtener datos de estas hojas o libros dinámicamente en otra hoja. La función INDIRECT puede ayudarte a hacerlo rápidamente.

Referenciar celdas en otra hoja de trabajo dinámicamente
Referenciar celdas en otro libro dinámicamente

Referenciar celdas en otra hoja de trabajo dinámicamente

Supongamos que hay cuatro hojas de trabajo que contienen diferentes ventas trimestrales para cuatro vendedores, y deseas crear una hoja de resumen para extraer dinámicamente las ventas trimestrales según el vendedor correspondiente. Para lograrlo, la siguiente fórmula puede ayudarte.

Fórmula genérica

=INDIRECTO("'"&nombre_hoja&"'!Celda desde donde se obtendrán los datos")

1. Como se muestra en la siguiente captura de pantalla, primero necesitas crear la hoja de resumen ingresando los nombres de las hojas por separado en diferentes celdas, luego selecciona una celda en blanco, copia la siguiente fórmula en ella y presiona la tecla Intro.

=INDIRECTO("'"&B3&"'!C3")

Notas: En el código:

• B3 es la celda que contiene el nombre de la hoja desde donde se extraerán los datos;
• C3 es la dirección de la celda en la hoja específica desde donde se extraerán los datos;
• Para evitar devolver un valor de error si B5 (la celda del nombre de la hoja) o C3 (la celda desde donde se extraerán los datos) están vacíos, encierra la fórmula INDIRECTO dentro de una función SI como se muestra a continuación:
  =SI(O(B3="",C3=""),"",INDIRECTO($B$3&"!C3"))
• Si no hay espacios en los nombres de tus hojas, puedes usar directamente esta fórmula
  =INDIRECTO(B3&"!C3")

2. Luego, arrastra su controlador de relleno hacia abajo para aplicar la fórmula a otras celdas. Ahora has devuelto todas las ventas del primer trimestre desde las hojas específicas.

3. Continúa extrayendo todas las ventas de otros trimestres según sea necesario. Y no olvides cambiar la referencia de la celda en la fórmula.

Referenciar celdas en otro libro dinámicamente

Esta sección trata sobre cómo referenciar dinámicamente celdas en otro libro en Excel.

Fórmula genérica

=INDIRECTO("'[" & Nombre_libro & "]" & Nombre_hoja & "'!" & Dirección_celda)

Como se muestra en la siguiente captura de pantalla, los datos que deseas devolver están en la columna E de la hoja de trabajo “Total ventas” en un libro separado llamado “SalesFile”. Sigue los siguientes pasos para lograrlo.

1. Primero, llenemos la información del libro (incluyendo el nombre del libro, el nombre de la hoja y las celdas de referencia), desde donde extraerás los datos hacia el libro actual.

2. Selecciona una celda en blanco, copia la siguiente fórmula en ella y presiona la tecla Intro.

=INDIRECTO("'["&$B$3&"]"&$C$3&"'!"&D3)

Notas:

• B3 contiene el nombre del libro desde donde deseas extraer los datos;
• C3 es el nombre de la hoja;
• D3 es la celda desde donde extraerás los datos;
• El valor de error #¡REF! se devolverá si el libro referenciado está cerrado;
• Para evitar el valor de error #¡REF!, encierra la fórmula INDIRECTO dentro de la función SI.ERROR como sigue:
  =SI.ERROR(INDIRECTO("'["&$B$3&"]"&$C$3&"'!"&D3),"")

3. Luego arrastra el controlador de relleno hacia abajo para aplicar la fórmula a otras celdas.

Consejo: Si no quieres que el valor devuelto se convierta en un error después de cerrar el libro referenciado, puedes especificar directamente el nombre del libro, el nombre de la hoja y la dirección de la celda en la fórmula de esta manera:
=INDIRECT('[SalesFile.xlxs]Total sales'!E3,"")

Función relacionada