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

Supongamos que tiene datos con el mismo formato en varias hojas de cálculo o libros de trabajo y necesita obtenerlos dinámicamente en otra hoja. La función INDIRECTO puede ayudarle a hacerlo rápidamente.

Hacer referencia a celdas en otra hoja de forma dinámica
Hacer referencia a celdas en otro libro de trabajo de forma dinámica

Hacer referencia a celdas en otra hoja de forma dinámica

Supongamos que dispone de cuatro hojas de cálculo con las ventas trimestrales de cuatro comerciales distintos y desea crear una hoja resumen que extraiga dinámicamente las ventas por trimestre según el comercial correspondiente. Para ello, puede utilizar la siguiente fórmula.

Fórmula genérica

=INDIRECT(")'«&sheet_name&»'!Cell to return data from")

1. Tal como se muestra en la siguiente captura de pantalla, primero debe crear la hoja resumen introduciendo los nombres de las hojas en celdas distintas. A continuación, seleccione una celda vacía, copie la fórmula siguiente en ella y pulse la tecla Entrar.

=INDIRECT(«'»&B3&«'!C3»)

Notas: En el código:

• B3es la celda que contiene el nombre de la hoja de la que extraerá los datos;
• C3es la dirección de la celda en la hoja de cálculo específica de la que extraerá sus datos;
• Para evitar que se devuelva un valor de error si B5 (la celda del nombre de la hoja) o C3 (la celda de la que extraerá los datos) están vacías, incluya la fórmula INDIRECTO dentro de una función SI, tal como se muestra a continuación:
  =IF(OR(B3="",C3=""),"",INDIRECT($B$3&"!C3"))
• Si los nombres de sus hojas no contienen espacios, puede utilizar directamente esta fórmula
  =INDIRECT(B3&«!C3»)

2. A continuación, arrastre el controlador de relleno hacia abajo para aplicar la fórmula a otras celdas. Así obtendrá todas las ventas del primer trimestre de las hojas de cálculo correspondientes.

3. Continúe extrayendo todas las ventas de los demás trimestres según lo necesite. Y no olvide modificar la referencia de celda en la fórmula.

Hacer referencia a celdas en otro libro de trabajo de forma dinámica

Esta sección explica cómo crear referencias dinámicas a celdas de otro libro de trabajo en Excel.

Fórmula genérica

=INDIRECT(")'[« & Book name & »]« & Sheet name & »'!" & Cell address)

Tal como se muestra en la siguiente captura de pantalla, los datos que desea obtener se encuentran en la columna E de la hoja de cálculo «Ventas totales» de un libro de trabajo independiente llamado «ArchivoVentas». Siga los pasos que se indican a continuación para realizarlo.

1. En primer lugar, rellene la información del libro de trabajo (incluyendo el nombre del libro, el nombre de la hoja de cálculo y las celdas de referencia) en el que se basará la extracción de datos en el libro de trabajo actual.

2. Seleccione una celda vacía, copie la siguiente fórmula en ella y pulse la tecla Entrar.

=INDIRECT(«'[»&$B$3&«]»&$C$3&«'!»&D3)

Notas:

• B3contiene el Nombre del libro de trabajo del que desea extraer los datos;
• C3es el nombre de la hoja;
• D3es la celda de la que extraerá los datos;
• El valor de error #¡REF!se devolverá si el libro de trabajo al que se hace referencia está cerrado;
• Para evitar el valor de error #¡REF!, incluya la fórmula INDIRECTO dentro de la función SISERROR tal como se indica a continuación:
  =IFERROR(INDIRECT(«'[»&$B$3&«]»&$C$3&«'!»&D3),«»)

3. A continuación, arrastre el controlador de relleno hacia abajo para aplicar la fórmula a otras celdas.

Consejo:Si no desea que el Valor de devolución se convierta en un error tras cerrar el libro de trabajo al que se hace referencia, puede especificar directamente el Nombre del libro de trabajo, el Nombre de la hoja de cálculo y la dirección de la celda en la fórmula de la siguiente manera:
=INDIRECT('[SalesFile.xlxs]Total sales'!E3,«»)

Función relacionada