Fórmulas que hacen referencia a otro libro Excel diferente.

Ya en una ocasión anterior comentamos cómo tener varios libros abiertos y trabajar con ellos, >ver aquí<.

Comentábamos entonces que podemos tenerlos abiertos simultáneamente y mostrándolos en la pantalla en mosaico, comparar datos contenidos en uno y en otro, o compartir datos en fórmulas. Supongamos que tenemos los siguientes libros:

Imagen

A la izquierda tenemos una tabla de datos con unos artículos. Cada fila muestra un identificador del artículo, su nombre y el precio por unidad correspondientes. En el libro de la derecha suponemos una serie de facturas. Solo queremos poner los datos: fecha de la factura, cantidad y el identificador del artículo. Queremos que el resto de datos aparezcan de forma automática, extrayendo dichos datos del otro libro.

En la primera factura, por ejemplo, ocupa la fila dos, hemos puesto la fecha: 12/01/2014 y el id de artículo 1. A continuación queremos que el nombre del artículo se encuentre automáticamente en la lista de datos y lo muestre en la celda C2. Procederemos de la forma siguiente:

  • Colocamos el cursor en la celda C2
  • Escribimos el signo =
  • Escribimos la función Buscarv (se puede ver una explicación de Buscarv más detallada > pulsando aquí <)
  • Abrimos los paréntesis de la función, y como primer argumento escribimos el valor buscado, B2, la celda a la izquierda en el libro de facturas. Se trata del id concreto que buscamos
  • El segundo argumento será la tabla dónde se debe buscar el id. Para hacerlo, sólo tenemos que hacer clic en el libro de Artículos y seleccionar el rango de la tabla de datos, es decir, A2:C9. Observamos que delante del rango aparece el nombre del libro y el nombre de la hoja que lo contiene. Será algo similar a ésto: [articulos.xlsx]Hoja1!$A$2:$C$9.
  • Por último, el tercer argumento es un número que corresponde a la columna en que se encuentra el dato que queremos extraer. Como en C2 queremos que aparezca el nombre del artículo y en la tabla de datos es la segunda columna, ponemos un 2.

Resumiendo, la fórmula en C2 aparecerá finalmente como sigue:

=BUSCARV(B2;[articulos.xlsx]Hoja1!$A$2:$C$9;2)
Ahora en esta celda aparece el nombre del artículo correspondiente al Id uno: Libreta. Y aún cerrando el libro de Artículos, ese vínculo permanecerá. Es más, si cambiáramos la descripción en el origen (artículos), se cambiaría en el libro facturas.
A continuación escribiremos una fórmula muy similar para extraer el precio por unidad del artículo en la celda E2:
=BUSCARV(B2;[articulos.xlsx]Hoja1!$A$2:$C$9;3)
En este caso solo cambiamos el tercer argumento, ya que el id a consultar y la tabla de datos están en el mismo sitio, son los mismos datos. Sin embargo, lo que queremos extraer es el precio, situado en la tercera columna.
Finalmente introducimos la fórmula para calcular el total, que será la cantidad por el precio unitario: =D2*E2
Estas fórmulas las podemos copiar hacia abajo. Obtenemos el resultado siguiente:
libros2

Si en el momento de introducir las fórmulas de BuscarV el Id del artículo estuviera en blanco, te aparecerá un error #N/A que desaparece en el momento en que pongas algo en esa celda. No te preocupes demasiado por ello.

Como ya comenté más arriba, si cambias un dato en el libro artículos, se reflejará en el de factura, pues existe un vínculo entre ellos. Por ejemplo si cambias el precio de las libretas a 15, cambiará también el importe de la factura. Puede ser que llegado un momento necesites “romper” ese vínculo, y evitar de esa manera la actualización. Para ello utiliza la opción dentro de Datos en la cinta de opciones llamada Editar vínculos. Procede de la forma siguiente:

  • Sitúate en el libro que contiene las referencias, el de Facturas
  • Selecciona Datos y Editar vínculos.
  • Si existen varios vínculos a distintos libros, selecciona los que quieres modificar.
  • Pulsa el botón Romper vínculos.

A partir de ese momento desaparecerán las fórmulas que hacían referencia al libro artículos y comprobarás que se ha cambiado por el valor correspondiente. Es decir, en lugar de =BUSCARV(B2;[articulos.xlsx]Hoja1!$A$2:$C$9;2) en la celda aparece Libretas. Ahora aunque cambies en Artículos, el libro Facturas continuará mostrando Libretas.

Anuncios

10 respuestas a “Fórmulas que hacen referencia a otro libro Excel diferente.

  1. Buen dia, como se puede hacer referencia a un libro de excel pero con extension .xlsm…? (Habilitada para macro) cuando intento hacer referencia a un libro con esta extension, me da error…

    • Hola Joan, gracias por pasar por aquí y comentar.
      Pues realmente el hecho de que el libro tenga macros no debería influir. Es más, lo acabo de probar con algunos ejemplos, y funciona perfectamente. Yo comprobaría si la fórmula está correcta, si el libro no se ha movido, cambiado de nombre, etc.
      Una cosa que he comprobado que sí suele dar problemas, aunque no errores, sino simplemente no te deja hacer la fórmula, es que abras dos veces Excel y cada una de las ventanas del programa tenga un libro diferente. La manera de que funcione es abriendo Excel una sola vez y luego haciendo dos veces (o las que sean) Archivo/Abrir por cada libro. De todas formas, ya digo que no es que dé error, es que simplemente encuentras que la fórmula no se puede escribir.

  2. Hola muchas gracias por tener este foro. tengo una pregunta que quisiera saber si me pueden ayudar a solventar: Tengo varios archivos de excel vinculados a un unico archivo que guarda varia informacion general. Supongamos que tengo un Archivo A el cual en la celda C1 tiene la informacion refereida a la celda B2 de un archivo B, ese es el escenario. Pero luego el Archivo B sufre un cambio, una fila fue agragada y la informacion contenida en la celda B2 paso a la celda B3… aqui surge mi problema. Como hago para que mi archivo A siga ese cambio y sepa que ahora tiene que referirse a la celda B3. Me pasa que al archivo B le estaran agregando y quitando filas entonces tengo que lograr el el archivo A sepa que tiene que seguir la informacion hacia donde se mueva luego de que filas sean agregadas o eliminadas.

    Muchas gracias!!!!

    • Hola Francisco, gracias a ti por tus comentarios.
      Efectivamente, si haces cambios en el archivo que tiene los datos origen, la fórmula pierde la referencia, y además da igual que en este caso hagas la fórmula relativa, que sería lo lógico.
      Una forma que he encontrado de que sí se actualicen es manteniendo ambos archivos abiertos mientras mueves los datos del origen o insertas líneas. Es decir que si vas a modificar la posición de la celda B2 del archivo B, cuando vayas a hacerlo, abre también el archivo A. En tal caso sí se actualizan las referencias.
      Otra cosa, utiliza archivo/abrir o recientes, para abrir los archivos, no uses doble click para hacerlo.
      Espero que te ayude.
      Saludos

  3. Hola, muy útil toda la info! Gracas! Pero tengo una pregunta, si o sí debo tener abiertos los libros a los cuales hago referencia en el libro en el que estoy trabajando? Es decir si tengo los libros “Datos”, “Stock” y “Clientes”, y resumo todos ellos aplicando cálculos en el libro “Balance semanal”… Debo tener siempre abiertos todos los libros?

    • Hola Seba, gracias por pasar por aquí y dejar un comentario.
      En realidad tanto como necesario…necesario no es, pero sí es mucho más práctico e incluso sencillo. Mira, el problema que tienes cuando haces referencia a libros que están cerrados es que para escribir la fórmula tendrías que escribir correctamente lo que se llama el path en que se encuentra, es decir la forma de llegar a él. En mi caso, por ejemplo, para referir un libro que se encuentra en el escritorio y que se llama cuentas, extrayendo el valor de la celda A1 y hoja Enero, tendría que escribir la fórmula siguiente: =’C:\Users\Equipo\Desktop\[cuentas.xlsx]Enero’!$A$1
      Como verás, acordarse de la fórmula y escribirla correctamente, con sus comillas, corchetes, etc. puede ser más complicado que simplemente cambiar de ventana y hacer un clic.
      Espero que te ayude
      Saludos

      • Claro! Entiendo que en la etapa de diseño de cada libro es mucho más práctico tenerlos abiertos (ya estuve haciendo prueebas). Pero me preguntaba si a la hora de actualizar celdas en las hojas de datos (clientes, stock, ventas), Excel maneja bien actulizados los resultados (libro “balance semanal” por ejemplo)

      • Si el cambio consiste simplemente en variar el valor contenido en la celda referenciada (por ejemplo en una celda de clientes cambias una fecha por otra), no debería haber problema, cuando abras el libro que lo recoge (el balance), se tiene que actualizar sin más. Incluso aunque el libro origen del dato (clientes) esté cerrado, en cuyo caso verías la fórmula precedida por ‘C:\….\[clientes.xlsx]’ o la ubicación del archivo, aunque con el valor correcto .
        El problema lo puedes encontrar cuando ese valor cambia de posición, por ejemplo que se mueve al insertar o eliminar una fila. Para este último caso, tal y como veíamos en el comentario de arriba (el de Francisco) sí es conveniente tener los libros abiertos mientras insertas o eliminas la fila.
        Espero haber aclarado tu duda
        Saludos

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión /  Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )

w

Conectando a %s