sábado, enero 20, 2007

Vinculos entre hojas Excel

Esta nota viene a colación de una pregunta de un compañero de trabajo sobre los vínculos entre hojas de distintos cuadernos en Excel.

Excel permite crear vínculos entre celdas de distintos cuadernos. Esto permite usar cuadernos de Excel en forma similar a una base de datos. Por ejemplo, en un cuaderno tenemos una lista de productos con sus respectivos precios; en una hoja de otro cuaderno manejamos el inventario de los productos. Podemos crear un vínculo en el cuaderno del inventario de manera que cada vez que actualicemos los precios, el valor del inventario se actualice.

Estos vínculos pueden funcionan también si el libro de origen (la lista de pecios en nuestro caso) está cerrado. Pero Excel también nos permite decidir si queremos que está actualización se haga en forma automática o no.

Veamos todo esto con un ejemplo. Supongamos esta lista de precios (de la base da datos NorthWind que viene con el paquete Office)



En una hoja de otro cuaderno tenemos una lista de inventario, como esta


Para crear vínculos a la lista de precios podemos:

1 – copiar el precio del producto y pegarlo en la lista de inventario con Pegar Vínculos


2 – o sencillamente seleccionar la celda dónde queremos crear el vínculo, en la barra de fórmulas introducir el signo "=", pasar al cuaderno de origen (Lista de precios), apuntar a la celda correspondiente y aceptar.

En ambos casos Excel crea un vínculo al cuaderno de origen, que aparece en la barra de fórmulas entre corchetes:


Cuando el libro de origen está cerrado, la referencia en la barra de fórmulas incluirá también la ubicación del archivo


La forma en que Excel maneja la actualización de los vínculos puede ser controlada a través del menú Herramientas—Opciones—Modificar


Si marcamos la opción Consultar al actualizar vínculos automáticos, cuando abramos el cuaderno que contiene el vínculo, veremos este mensaje


En caso contrario, Excel actualiza los vínculos automáticamente.

Cuando ambos cuadernos están abiertos, cambios en la celda de origen se reflejan inmediatamente en la celda que contiene el vínculo.

También podemos manejar los vínculos mediante el menú Edición--Vínculos


Por ejemplo, si queremos romper todos los vínculos en una sola operación, usamos el botón Romper vínculos. Al usarlo recibimos esta advertencia, ya que no es posible deshacer esta operación


Para romper el vínculo de una celda solamente, podemos usar Pegado Especial--Valores

Si esto sucede estando el cuaderno vinculado cerrado, al abrirlo recibimos un mensaje que el cuaderno no puede actualizarse


Si elegimos actualizar los vínculos, recibimos otro mensaje


Al apretar el botón Modificar se abrirá el diálogo de Modificar vínculos que nos permitirá crear el vínculo al archivo relevante.


Technorati Tags:

110 comentarios:

  1. HOLA !
    ME PARECE
    EXCELENTE EL ARTICULO !

    TENGO UNA CONSULTA : SI TUVIERA UN INVENTARIO Y UNA LISTA DE ARTICULOS EN OTRA PLANILLA ( SUBCONJUNTO DE LOS ARTICULOS DEL INVENTARIO) A LOS QUE DEBO ACTUALIZAR EL PRECIO FRECUENTEMENTE, QUE ES LO QUE ME CONVIENE UTILIZAR ???


    SALUDOS!

    MUCHAS GRACIAS !

    ResponderBorrar
  2. Hola! Excelente blog ! Felicitaciones!

    Una Consuta :

    Si tuviera que actualizar frecuentemente un inventario, a partir de una lista de precios para determinados articulos de ese invetario, cual es la mejor forma de hacerlo automaticamente ??????

    Saludos y Muchas Gracias!

    ResponderBorrar
  3. Hola Gabriela,

    puedes usar BUSCARV creando una referencia a la hoja donde actualizas los precios. Digamos que ne la hoja1 tienes el inventario y es donde actualizas los precios y en la hoja2 tienes el subconjunto, la fórmula en la hoja 2 sería
    =BUSCARV(número de artículo;rango Hoja1;2;0)

    ResponderBorrar
  4. Como actualizar una lista (precios, inventario o lo que fuere) depende del diseño del modelo. Por ejemplo, en la pregunta de Gabriela, la actualización ocurre inmediatamente cuando cambiamos los precios en la hoja1.
    Otra manera sería con macros y un evento como WorkBook_Open que los haría correr cada vez que se abra el cuaderno.

    ResponderBorrar
  5. Hola! Gracias por su respuesta, me resulta de mucha ayuda !

    Consulta :

    En caso de tener un libro en el cual las hojas representan facturas, con sus respectivos totales calculados en una determinada celda,
    ¡¿Como puedo hacer para obtener una sumatoria de dichos totales ???

    Muchas Gracias !

    Saludos

    ResponderBorrar
  6. Hola Gabriela
    la forma más directa, pero no muy eficiente, es crear una formula SUMA cuyas variables son la celda correpondiente en cada hoja.
    Excel no tiene fórmulas "tridimensionales" pero hay ciertos atajos. El tema excede el marco de un comentario y cuando vuelva de mis vacaciones espero terminar una nota sobre el tema.
    Y al margen tu pregunta, una hoja para cada factura? Parece muy "pesado".

    ResponderBorrar
  7. hola !!!

    tengo un problemita y si me ayudas te lo agradecere enormemente.
    tengo que vincular varios precios(de ditintas celdas)de la hoja dos, a una celda de la hoja uno.
    hasta el momento lo hago seleccionando la celda de la hoja dos y con "=" lo vinculo a la hoja uno pero es muy lento y son muchisimos precios y no se como tomar varios a la vez y vincularlos todos al mismo tiempo a la celda matriz de la hoja uno.

    de ante mano gracias.
    estoy desesperado!!!!!!!!!!

    ResponderBorrar
  8. Hola Santiago

    típicamente tendrías que usar una función de búsqueda como BUSCARV.
    Puedes mandarme una copia del archivo para que me haga una idea más cabal de tu problema?

    ResponderBorrar
  9. Hola, tengo una preguna:
    Tengo en una hoja una lista de precios con un "codigo" para cada producto, y en otra hoja tengo un formulario para imprimir facturas, ¿como puedo hacer que al escribir el codigo del producto me ponga los datos automaticamente?

    ResponderBorrar
  10. Hola Luis

    creando un vínculo al número de código usando la función BUSCARV. En la celda del código puedes usar Validación de datos con la opción lista, como he mostrado en algunas notas.
    También se puede hacer con una macro de tipo eventos.

    ResponderBorrar
  11. Mi consulta es que tengo un problema cuando tengo un libro r, al que otros hacen referencia e inserto una fila en una hoja del libro r, si los demás libros que se vinculan con r no están abiertos, las fórmulas no se actualizar aunque actualice los vínculos, sí se actualizan los valores de las celdas que están comprendidas en el rango inicial, pero no se desplanzan los rangos en las fórmulas de los libros que referencian a r. Por el momento la única opción que me fue sugerida y funcionó, es que el rango en el libro r esté definido bajo un nombre.

    ResponderBorrar
  12. Buena la idea del blog, espero que se potencie con los detalles que a veces causan problemas, en este caso, con los vínculos. Mi situación es la siguiente, tengo dos libros, uno con detalles y el otro con totales. En el libro de destino, totales, almacena los totales de la cantidad de productos comprados. Luego, al final de totales, aparece una suma. O sea, estoy sumando los datos en una columna, pero estos datos están vínculados al libro detalles. Al usar la suma, el resultado es 0.

    Entiendo que "suma" quiera tomar los valores de las celdas y que al encontrar algo como ='detalles!... no sume nada, pero cómo hacer para que sume los valores vinculados y no la fórmula de vinculación?

    ResponderBorrar
  13. No estoy seguro de entender lo que describes.
    Si tienes una fórmula que se vincula a otra cuaderno, la fórmula =SUMA(otrocuaderno.xls!A1:A100) tendría que dar una resultado distinto de cero, suponiendo que hay valores en el rango vinculado.
    Puedes mandarme ambos cuadernos para que me haga una idea de cuál pueda ser el problema.

    ResponderBorrar
  14. Hola, felicitaciones gran blog. Te tengo una consulta. Que ocurre cuando ambos libros estan abiertos y los cambios que hagan en el libro 1 se refleje de forma automatica en el libro 2 teniendo ambos archivos en distintas computadoras. Es decir si en la computadora2 el libro2.xls la Hoja1 la celda A2 se cambio a 500(precio de algo y logicamente guardando) y en el Libro1.xls que esta vinculado pero en la computadora1 tambien se refleje el mismo valor es decir 500.

    Se que esto es posible si cierro el libro1 y lo vuelvo abrir me sale la opcion ACTUALIZAR y listo pero lo que deseo es evitar esto ya que la idea es que sea mas practico y actualice en linea.

    Espero sea clara mi consulta

    Gracias

    Saludos

    ResponderBorrar
  15. Hola
    las máquinas tienen que estar vinculadas (participar en la misma red) si no no habría forma que la fórmula en un cuaderno pueda "ver" dónde está la referencia.
    Para que los vínculos se actualicen automáticamente tienes que cambiar las definiciones en Opciones como señalo en la nota.

    ResponderBorrar
  16. Hola Jorge.
    Una consulta, tengo un excel vinculado a varios archivos. Si estos archivos estan cerrados, no me muestra los datos y da un error en las celdas, pero si los abro a todos los archivos, si me muestra los datos. Aclaro que los archivos estan en red.
    Sds.

    ResponderBorrar
  17. Wally

    hay funciones que dan error si el cuaderno remoto no está abierto (como INDIRECTO). Qué funciones estás usando en el vínculo?

    ResponderBorrar
  18. Hola
    excelente blog!!!
    ayuda amuchos a sacarnos de la ignorancia je je je
    tengo una duda, tengo dos hojas de excel A y B en A capturo varios datos llamense nombre, apellido, edad, direccion, telefono y estado y en la hoja B solo requiero direccion y telefono por decir, para que B actualize en forma automatica estos campos es decir sin tener que volver a capturar o hacerlo en forma manual ctrl+c y ctrl+v existe alguna formula que lo realize en forma automatica? hay que tomar en cuenta que la lista de A va ir creciendo es decir metere mas filas con el paso del tiempo y requiero que en B se vean reflejados de igual forma y se vallan acomulando asi como en la hoja A.
    espero haberme dado a entender!!! gracias de antemano

    ResponderBorrar
  19. Hola Rafael

    para hacerlo con fórmulas tendrías que poner en la hoja B referencias a las celdas de la hoja A ququieres que aparezcan en la primera. Esto implicaría crear las referencias de antemano o agregarlas cada vez que haga falta.
    Otra alternativa es programar un evento de manera que cada vez que escribís un datos en A, la macro lo copie a B.

    ResponderBorrar
  20. Hola Jorge
    Tengo una pregunta acerca de los vinculos de excel.
    Yo tengo el mismo caso libro 1 (origen de datos) y libro 2 (resultado de datos)
    En el libro 2 si pongo una referencia en la celda A1, entonces la celda A2 busca un precio en el libro 1.
    Mi sorpresa viene cuando envio el libro 2 por email, los destinatarios pueden cambiar la referencia y actualizar los precios sin haber recibido el libro 1. Como es posible?
    Que hay que hacer para que esto no ocurra?
    Ademas si el receptor utiliza MAC recibe un fichero adjunto con los codigos y datos del libro 1.
    Agradeceria sus comentarios.
    Slds
    Miguel

    ResponderBorrar
  21. Por supuesto, todo contenido de una celda que no esté protegida puede ser cambiado. Pero no tengo claro qué es lo que quieres decir con "actualizar los precios".

    ResponderBorrar
  22. Hola Jorge, muy útil tu blog!!!
    quisiera hacer una consulta, supongamos que en la hoja 1,
    tengo para ingresar los datos: fecha, cantidad, costo, total y comentario.

    y en la hoja 2 tengo armado un recibo de pago, como hago para que los datos en la hoja 1, se escriban en la hoja 2, se imprima el recibo (de manera doble, uno para el taller o proveedor que me entrego)
    y luego se borren de la hoja 2 y queden liberados para la próxima entrada de datos de la hoja 1.

    ResponderBorrar
  23. Ivanda,
    para ese tipo de tareas tienes que usar eventos (macros). Si no estás familiarizado con el tema puedes hacer una búsqueda en el blog con la palabra "eventos" o con la palabra "factura".

    ResponderBorrar
  24. Luis

    Hola Jorge,

    Ante todo, muchas gracias por el blog.

    Mi problema... Tengo un libro vinculado con funciones a otro. Cuando cierro el de origen, todas las celdas que contienen funciones "sumar.si" dan error. Tengo otras funciones "buscarv" que siguen mostrando los resultados, aún estando cerrado el libro de origen. He visto por aquí que la función "sumar.si" no funciona con libros cerrados.

    Por tanto, mi pregunta es: ¿que función o funciones podría usar en lugar de "sumar.si"?

    He estado intentándolo con "suma(si(...", pero no con sigo que me de el resultado correcto. No me está teniendo en cuenta los criterios de suma que quiero introducir. Por si sirve, estos criterios forman una cadena con celdas en el libro de destino + palabras (texto).

    Muchas gracias por tu ayuda!

    ResponderBorrar
  25. Te sugiero que lo intentes con SUMAPRODUCTO.

    ResponderBorrar
  26. Luis

    Hola de nuevo Jorge,

    Sí, lo he estado intentando también con "sumaproducto", pero no consigo obtener los resultados. Lo he cambiado de varias formas, pero siempre me da error.

    Esta es una de las funciones originales con "sumar.si":

    =SUMAR.SI('R:\CdM\[RH10.xls]Rotación & Headcount'!$C$2:$C$65536;$B35&" "&"AGE"&" "&"ALTA"&" "&F$7;'R:\CdM\[RH10.xls]Rotación & Headcount'!$I$2:$I$65536)

    ¿Podrías decirme cómo hacerla con "sumaproducto"?

    Muchas gracias de antemano.

    ResponderBorrar
  27. Luis

    Hola de nuevo,

    Sinceramente, esa nota no me aclara nada sobre el uso que le quiero dar a la función "sumaproducto". Lo que yo quiero hacer, según la función que he pegado anteriormente, es contar o sumar los números "1" que tengo en la columna "I", siempre que cumplan la condición expuesta, que es una cadena de texto en la tabla de origen.

    Como digo, agradezco la referencia a esa nota, pero esta es demasiado simple y escueta como para resolerme algo.

    Si no se te ocurre otra cosa, seguiré viendo la forma de hacerlo de otro modo.

    Muchas gracias.

    ResponderBorrar
  28. Hola .. tengo un archivo de excel que tiene varias hojas, cuando lo abro me pregunta por los vinculos a otro archivo pero yo no cree vinculos con otro archivo sino formulas entre las hojas del mismo archivo .. no se si fue que me equivoque en algun lado .. como hago para encontrar la o las celdas que tienen la referencia al otro archivo ??

    ResponderBorrar
  29. En Excel Clásico (97-2003), con el menú Edición-Vínculos, tal como muestro en esta nota. En Excel 2007 la opción está un poco escondida: botón de Office-Preparar-Ejecutar el comprobador de compatibilidad.
    Hay que tener en cuenta que los vínculos a otros archivos pueden estar no sólo en fórmulas en las celdas sino también en nombres definidos.

    ResponderBorrar
  30. Buen dia.
    Como siempre : EXCELENTE TU BLOG!
    Quisiera saber si existe la posibilidad de "ocultar" los vinculos a otros libros; ya sea por una cuestion de seguridad o para preservar la privacidad del origen de la informacion.
    Desde a muchas gracias por tu excelente y abundante aporte.

    ResponderBorrar
  31. José,
    en formato de celdas usar las opciónes "Bloqueada" y "Oculta" en la pestaña Proteger. Luego al proteger la hoja las fórmulas no son visibles.

    ResponderBorrar
  32. Saludos, Soy Ainnara y encontré este blog super interesante, y necesito una ayuda por favor:

    En mi caso, al realizar vinculos con otros libros u hojas, tengo la dificultad de que al insertar una fila o columna en el documento origen, se me daña la formula del vinculo y como el formato de la informacion va a necesitar insertar constantemente filas o columnas quisiera saber si se puede fijar el vinculo sin que afecte el resultado de la formula, aunque se mueva de lugar.

    Muchas gracias y espero su ayuda por favor.

    ResponderBorrar
  33. Hola Ainnara,
    la solución es usar nombres que se refieran a los rango en el cuaderno de origen. El tema es un poco extenso para el marco de un comentario así que estaré publicando una nota sobre el tema.

    ResponderBorrar
  34. Jose Riu, 10 abril, 2012 15:26
    Buen dia.
    Como siempre : EXCELENTE TU BLOG!
    Quisiera saber si existe la posibilidad de "ocultar" los vinculos a otros libros; ya sea por una cuestion de seguridad o para preservar la privacidad del origen de la informacion.
    Desde a muchas gracias por tu excelente y abundante aporte.

    Jorge L. Dunkelman, 10 abril, 2012 20:46
    José,
    en formato de celdas usar las opciónes "Bloqueada" y "Oculta" en la pestaña Proteger. Luego al proteger la hoja las fórmulas no son visibles.

    Hola Jorge, perdon por la demora en repreguntar, pero me volvio surgir la duda, y encontre que ya l ohabia consultado anteriormente, pero la solucion no es la deseada.
    Lo que preciso es poder ocultar los vinculos a otros libros, aun en la pestaña de "datos/editar vinculos".
    Tambien me interesaria que no se pudieran ver las conexiones a datos externos de la pestaña "datos/conexiones"
    Desde ya muchisimas gracias por tu excelente aporte.

    ResponderBorrar
  35. Hola Jorge: Soy Javier, no sé como hacer para modificar el código de un formulario Google Docs.
    Mi trabajo requiere que en tiempo real vaya enviando formulario compuesto por 10 campos, y por otra parte yo preciso de mi propio formulario compuesto por 15 campos en los que los 10 campos primeros coinciden, por lo que me ahorraría mucho tiempo el que rellenara las 2 tablas de una sola pasada. Crees que es posible?. Mil gracias de todas todas.

    ResponderBorrar
  36. Mil disculpas por la demora en responder. No conozco ningún método directo para hacer que los vínculos no aparezcan en "datos--editar vínculos". Tal vez se podría programar un evento que impida la apertura de la ventanilla "editar-vínculos".

    ResponderBorrar
  37. Javier,
    no me queda claro la tarea. ¿Ambas tablas están en Google Docs o se rellena una tabla en Google Docs y los datos pasan a otra tabla en Excel?

    ResponderBorrar
  38. Jorge, como andas?

    Vincule una carpeta A con la carpeta B, y la carpeta A tiene contraseña, entonces cuando abro la carpeta B me pide la contraseña de la carpeta A para actualizar los valores. Haciendo toda esta explicación correctamente, en algunas celdas me figura error (#N/A)y en las demás están bien.
    Que puedo hacer al respecto.

    Gracias.

    Saludos.
    Nicolás.

    ResponderBorrar
  39. ¿Que fórmulas hay en el cuaderno B? #NA siginifica que el valor buscado no existe en el cuaderno de origen.

    ResponderBorrar
  40. HOLa muchas gracias es un gran articulo me ayudo mucho

    ResponderBorrar
  41. Hola,
    me podrían ayudar con lo siguiente?
    Tengo una planilla destino donde manejo montos totales por proyecto con la funcion sumar.si.conjunto de otras planillas origen.
    Al estar las planillas origen abiertas me aparece el resultado, pero al estar cerradas me entrega #¡VALOR!.

    Agradecería su ayuda.

    ResponderBorrar
  42. Joaquín,
    hay ciertas funciones que exigen que el cuaderno remoto esté abierto (creo haber tocado el tema en alguna de las notas del blog). Hay varias soluciones para una situación como la que describes pero no puedo ponerlas en el marco de un comentario. Veré de publicar una nota sobre el tema.

    ResponderBorrar
    Respuestas
    1. Jorge estoy con el mismo problema de Joaquín como haría para solucionar ese error valor en la Fórmula si conjunto

      Borrar
  43. Hola!

    Tenemos que pasar todas las carpetas de un disco duro a otro y con más carpetas de por medio. Por ejemplo:

    de C:\Carpeta original

    a F:\División nueva\disco nuevo\Carpeta original)

    El problema está en que si hacemos eso, los vinculos que tenemos entre los distintos archivos excel en las distintas carpetas, dejarán de funcionar ya que la ruta será distinta.

    ¿Hay alguna manera de solucionar el problema?

    gracias!

    ResponderBorrar
  44. hola que buen blog, espero puedan ayudarme, como puedo hacer para que de un click en una celda que esta vinculada a otro archivo excel, automaticamente se abra este archivo excel al cual esta vinculado. Me acuerdo que una vez una amiga lo hizo configuracion en las opciones, pero estoy intentando y no puedo hacerlo, alguien me puede ayudar por favor?.....Ronald

    ResponderBorrar
  45. En la pestaña Archivo:
    Avanzadas--Opciones de edición
    quitar la marca de la opción "Permitir editar directamente en las celdas".
    Esto te permite abrir el cuaderno vinculado con un doble-clic.
    Para abrirlo con un solo clic, hay que definir un hipervínculo.

    ResponderBorrar
  46. Buenos días, me parece excelente el blog, me ha salvado de muchas cosas que no sabía hacer.

    Tengo además una pregunta. Tengo unos vínculos a un archivo pero me toca reemplazar algo dentro de la fórmula. El problema es que cada vez que lo hago Excel me pide que le de la ruta al libro (abre un cuadro de diálogo del cual lo debo seleccionar). Esto lo hace una vez por cada celda que intento reemplazar. Dado que son aproximadamente 1800 celdas, pueden ver que no me es óptimo darle siempre el archivo a cada una.

    Con una macro si le desactivo las alertas, simplemente en la celda me saca el error de #REF.

    ¿Qué podría hacer? ¿Si queda todo claro?

    ResponderBorrar
  47. ¿Qué es el algo que te toca reemplazar?

    ResponderBorrar
  48. Dentro de la fórmula hay una referencia absoluta que debo cambiar para que dirija al valor adecuado. Cuando lo hago es que me pide el archivo y lo hace para cada celda.

    ResponderBorrar
  49. Es difícil hacerme una idea precisa de lo que tienes que hacer, pero si se trata de editar 1800 fórmulas cada vez que hay que hacer un cambio, tu modelo no está construido en forma eficiente.
    Si se trata de trae datos desde distintas fuentes sería más eficiente consolidar los datos en una única fuente y si no se puede, crear fórmulas dinámicas. El marco de un comentario no da para una explicación detallada. Puedes mandarme tu consulta aportando más detalle (podrías mandar un cuaderno con un ejemplo de lo que quieres hacer). Fijate en el enlace Ayuda, en la parte superior de la plantilla.

    ResponderBorrar
  50. Hola, una consulta, tengo el problema que al vincular muchos libros (20) me tira el error de "formula demasiado larga" y no puedo seguir vinculando celdas. Hay alguna solución a esto o esta limitado? muchas gracias!

    ResponderBorrar
  51. Efectivamente, existen varias limitaciones a las fórmulas, entre ellas el número de caracteres (depende de la versión; 1024 en Excel 2003, 8192 en Excel 2010). Pero por encima del tema, en general es preferible evitar vínculos externos y más aun si son muchos. Creo que tendrías que enfocar el problema de otra manera.
    Uno de los problemas con los vínculos externos es que, por ejemplo, si cambias la ubicación de los archivos, tienes que editar todas las fórmulas.

    ResponderBorrar
  52. Hola me he leido todos los comentarios y consultas de este blog y me parecen interesantisimos pero sigo con una duda, en mi caso tengo dos libros, uno donde se encuentra la informacion primaria y en otro tengo los vinculos, pero como lo que quiero lograr es un reporte diario y automatizado sin la necesidad de intervenir y abrir los dos libros para que se actualicen automaticamente, no se si habra solucion a mi problema, espero y agradezco cualquier ayuda...

    ResponderBorrar
  53. La forma de hacerlo, sin abrir los cuadernos de origen, es programando un evento (Vba, macros), de manera que al abrir el cuaderno con el reporte, los datos se actualicen.

    ResponderBorrar
  54. hola Jorge,

    Pregunta yo tengo un archivo excel que quiero vincular solo con ciertos datos a una carpeta que va a estar en red, es posible hacer esto...si es el caso como se hace, lo que quiero es que mi archivo en excel solo modifique yo y el archivo que va vinculado con la informacion nadie lo modifique...

    ResponderBorrar
  55. Puedes cifrar (proteger el contenido con una contraseña) o restringir el ingreso al libro.

    ResponderBorrar
  56. hola Jorge,

    Como hago que se vincule de un archivo excel ciertas celdas o otro archivo excel en carpeta en red.

    Disculpa tanta pregunta.

    Gracias

    ResponderBorrar
  57. Hola, creando directamente la referencia (clic a la celda del cuaderno remoto), para lo cual, obviamente, este cuaderno debe estar abierto.

    ResponderBorrar
  58. Hola, tengo un problema, en mi clase de informatica tengo dos libros, en un libro una factura y en otro el inventario, en ambos hay un codigo un articulo una cantidad y una valor del articulo, como hago para que cuando este en la factura ponga un codigo y automaticamente me salga el articulo y su valor unitario ? te lo agradeceria

    ResponderBorrar
  59. Bien, no es un problema de vínculos sino de referencias. Excel tiene muchas funciones de búsqueda para ese tipo de tareas, como BUSCARV e INDICE.

    ResponderBorrar
  60. hola amigo tengo vinculados 5 archivos secundarios en un archivo principal, todos los archivos tienen contraseña para abrirlos, mi pregunta es, en el archivo principal existe alguna forma de que no me pida la contraseña de los archivos secundarios y se actualice automáticamente?

    ResponderBorrar
  61. SI se trata de abrir los archivos tendrás que usar la contraseña. Si se trata de actualizar los datos en el principal, depende qué funciones estás usando para traer los datos de los archivos secundarios. Otra forma sería extraer los datos de los archivos secundarios sin abrirlos usando ADO.

    ResponderBorrar
  62. Hola, qué tal? Felicitaciones por el blog! Mi consulta es bien sencilla. Por qué a veces sucede que, teniendo abiertos los libros A y B abiertos, al intentar referenciar una celda del libro A a una del B (con "=" o "+"), al posicionarme en el libro B no me deja hacerlo? (Es decir, no aparece en la barra del fórmulas el "=" o el "+". Muchas gracias!

    ResponderBorrar
  63. Si el orden de acciones es:
    - ingresar "=" en una celda del libro A
    - activar el libro B (Alt+TAB o Ctrl+TAB o con el mouse)
    no hay ningún motivo para que en la barra de fórmulas deje de estar en estado de edici´´on (es decir, aparezca "=")
    Por lo visto estás realizando alguna acción que cieera el libro A al pasar al B.

    ResponderBorrar
  64. Gracias por la respuesta! Por si aclara un poco más el caso, muchas veces pasa que teniendo un tercer libro C tamibén abierto, y en una celda del libro A ingreso "=", al posicionarme en el libro B la barra de fórmulas no está en estado de edición, pero al posicionarme en el libro C, esta sí lo está.
    Este es un problema que varios de mis compañeros y yo tenemos, de tanto en tanto, en mi oficina. La única solución que hemos encontrado es cerrar todos los libros y volverlos a abrir, lo cual resulta tedioso, claro está.
    De todos modos, agradezco mucho su blog en general, y la respuesta a mi pregunta en particular. Saludos!

    ResponderBorrar
  65. Es muy curioso la que estás describiendo. Sin ingresar "=" en ninguno de los libros, ¿la barra de fórmulas en el libro B es visible o se puede editar?

    ResponderBorrar
  66. Extacto, en el libro B puedo ingresar cualquier fórmula interna, digamos, es decir, que no haga ningún vínculo con otro archivo. Adicionalmente, digamos en un 75% de los casos, esta situación se presenta cuando el libro B se trata de un libro nuevo, o sólo lectura, que no está guardado. Pero también ha pasado en casos en los que el libro B se trata de la versión original de un libro guardado, por lo que esto no parecería ser la razón que explica el problema. Gracias!

    ResponderBorrar
  67. Hola tengo un problema, con la función CONTAR.SI, en un libro utilizo esta funcion contando contenidos de otros libros, el problema es que si no tengo los otros libros abiertos la función me da #VALOR, aún poniendo que actualice los vinculos, este error no me pasa con otras funciones como =BUSCARV. el Excel que utilizo en mi trabajo es el 2003 SP3.

    Gracias

    ResponderBorrar
  68. Ciertas funciones (CONTAR.SI, INDIRECTO, SUMA.SI, por mencionar algunas) requieren que los cuadernos con las referencias remotas estén abiertos. Hay rodeos para superar esta llimitación, pero no puedo extenderme en el marco de un comentario. Publicaré una nota sobre el tema.

    ResponderBorrar
  69. Muchas gracias Jorge.

    Saludos

    ResponderBorrar
  70. Buenas tardes felicitaciones por el blog! vengo con un pequeño inconveniente y es que tengo varias planillas una de origen y las demas con datos vinculados por la formula de buscarv, el tema es que al estar cerrado el archivo de origen cerrado me marca errores en las demas planillas con "#ref" en este caso si se encuentra abierto el archivo de origen funciona todo correctamente, ahora bien si se encuentra cerrado me figura el error, de que manera se pueden mantener los datos activos para evitar este error? ya que la idea de la vinculacion entre las planillas es que se trabajaran desde diferentes pcs, aunque esten todas las planillas en una sola carpeta en una pc, saludos!

    ResponderBorrar
  71. BUSCARV funciona bien tambien cuando tiene referencias a cuadernos cerrados, a diferencia de otras funciones como INDIRECTO, por ejemplo. ¿Puede ser que estés usando BUSCARV combinada con otra función o con una referencia a otra función?
    Fijate en esta nota, en especial a la función PULL allí mencionada.

    ResponderBorrar
  72. Hola, Un blog muy interesante. Tengo un problema con un libro donde tengo vinculos a dos libros diferentes. La funcion que los une es BUSCARV en ambos casos busca lo mismo en los dos libros y los pone en un mismo libro. Cuando actualizo los datos con los libros de origen abiertos no me da ningún problema, pero cuando actualizo con los libros cerrados, solo actualiza los datos de uno de los libros y el otro da error de valor no disponible #N/A, si abro el libro de origen y actualizo, se actualiza sin problema, y la verdad no tengo ni idea de porque puede ser. Muchas gracias.

    ResponderBorrar
  73. Hola Susana, fijate en mi comentario del 15 de setiembre y también en esta nota

    ResponderBorrar
  74. Hola, soy Carolina de Colombia, excelente tu blog te felicito, muy pero muy útil. Te hago una pregunta tengo varios archivos ligados a un solo consolidado todo me marcha bien pero no sé porqué a veces los vínculos "se vuelven locos" me explic, digamos que los archivos estan en la ubicación c:/octubre/archivos1.xls.... hasta c:/octubre/archivos16.xls y así aparecen formulados en el consolidado y correcto siempre, pero algunas veces cuando voy a ver los vínculos aparece como c:/users/docs/appdata/roaming/Microsoft/Excel/archivos1.xls todos los vínculos se cambian a esa ubicación no se porque y me toca empezar a cambiar el origen uno por uno de los archivos, me ha pasado varias veces no sé porqué pueda ser... Agradezco tu ayuda, saludos!

    ResponderBorrar
  75. Hola Carolina,
    fijate en esta nota, creo que responde a tu consulta.

    ResponderBorrar
  76. Excelente blog

    Una pregunta tengo un Archivo de excel vinculado a otro archivo, ese archivo tiene hipervinculos y cuando selecciono la opcion de actualizar todo se pierde ese hipervinculo, ¿existe alguna opcion para no perder ese hipervinculo?

    De antemano gracias

    ResponderBorrar
  77. ¿El hipervínculo apunta a una celda ocupada por una tabla dinámica?

    ResponderBorrar
  78. Buenas!!
    Enhorabuena por el blog!!
    Tengo una duda, y no se si tiene mucho que ver con este tema.
    Quiero copiar las formulas de un archivo, en lugar de su resultado. Es decir, en el libro 1 tengo por ejemplo las celdas A1 y B1 con valores 10 y 15; y en la celda A2, una formula para sumarlos, con resultado 25.
    En otro libro (libro 2) tengo en las celdas A1 y B1 los valores 3 y 4, por ejemplo; y en la celda A2 quiero copiar la formula A2 del libro 1, para que de el resultado de 7.
    Al hacerlo con los consejos que he leido arriba (DATOS - EDITAR VINCULOS) excel me dice que hago una referencia circular, y no consigo que en la celda A2 del libro 2 salga 7 en lugar de 25... alguna salida??
    Gracias de antemano!

    ResponderBorrar
  79. Estimado, suponiendo que en la fórmula no hay referencias a un cuaderno remoto, sencillamente Copiar-Pegar Fórmulas.

    ResponderBorrar
  80. Buenas otra vez!!
    El problema es que en lugar de ser una simple suma, se trata de unas cuantas formulas bastante complejas; y despues quiero que desde unos cuantos archivos distintos (son mas de 100, cada uno con diferentes datos) se pueda enlazar ese archivo que solo tiene formulas, pero que me coja solo las formulas, en lugar del resultado. Con esto quiero conseguir que si algun dia deseo cambiar algo en una formula, solo tenga que cambiarlo en el archivo que tengo las formulas, y no en todos y cada uno de los archivos donde tengo los datos.
    No se si me explico, yo creo que con el ejemplo anterior se entendia mejor; aunque tambien puede ser que sea imposible conseguirlo...

    ResponderBorrar
  81. El problema es de concepto, pero explicarlo me llevaría un post entero. La única forma que se me ocurre es que las fórmulas usen UDF (funciones definidas por el usuario). Si estas fórmulas están guardadas, por ejemplo, en el cuaderno Personal.xlsb entonces al modificar las funciones las fórmulas de todos los cuadernos reflejarían ese cambio.

    ResponderBorrar
  82. Una consulta:
    tengo una carpeta con cientos de ficheros Excel, ¿cómo podría obtener un listado que contenga el nombre del fichero Excel y la ruta completa de todos los vínculos que pueda contener ese fichero?
    Gracias,

    ResponderBorrar
  83. Si usas Excel 2010 o 2013 puedes instalar el complemento Power Query y usar la técnica que muestro en esta nota.
    En la misma nota hay enlaces a otras notas donde muestro distintas técnicas.

    ResponderBorrar
  84. Hola Jorge,

    Muchas gracias por ese blog!

    Tengo un problema con los vinculos que no logro resolver.
    Comparto documentos Excel con varios colaboradores en Dropbox, y he creado vinculos entre esos documentos. Digamos que el documento 2 saca datos del documento 1.
    En mi compu, la ruta de cada vinculo es la ruta en mi propia compu, que es diferente de la de mis colaboradores para esos mismos documentos.
    Al hacer pruebas con un documento muy simple, podia ver que al abrir el documento 2 en su compu, mis colaboradores podian ver que la ruta del vinculo se actualizaba con la ruta en su propia compu, y entonces si hacian modificaciones en el documento 1, se actualizaba bien en el documento 2.
    Pero desde que he convertido todos los documentos del formato xlsx en el formato xls, esa actualizacion de ruta (y entonces de vinculos) no funciona mas. O sea, al abrir el documento 2 en su compu, mis colaboradores ven en las celdas vinculadas la ruta de mi propia compu y no de la suya, y entonces no pueden actualizar en el documento 2 las modificaciones que hacen en el documento 1.

    Tendrias una explicacion y una solucion a eso?

    Te agradezco mucho!!

    ResponderBorrar
  85. No sabría decirte donde pueda estar el problema. Como parece ser que el problema está ligado a la conversión de los archivos, te sugiero que intentes recrear los vínvulos reescribiendo las fórmulas.

    ResponderBorrar
  86. Hola,
    Quisiera conocer cómo puedo hacer para que si cambio el precio de un producto en un momento dado, ya sea por un descuento casual, por una subida o bajada de precios, etc. no se me cambien todos los precios que ya se registraron en "vendidos" y que estaban ligados a ese precio.

    Gracias!

    ResponderBorrar
  87. La única forma es que los precios sean constantes, es decir, no resultados de fórmulas o de celdas ligadas.

    ResponderBorrar
  88. Hola,
    La situación es la siguiente, tengo un libro con 100 hojas, y tengo otro libro (2) cuyos datos provienen de cada una de las hojas existentes en el libro (1), es decir, el origen de los datos del libro (2) provienen de cada una de las hojas del libro (1).
    Como son muchas hojas en el libro (1) quiero crear uno o varios libros nuevos dividiendo las hojas del libro (1) porque se pone muy lento. Luego de copiar las hojas a un libro nuevo, quiero eliminar las hojas del libro (1) que ya copie. Como hago para eliminar esas hojas en el libro (1) de origen y no se modifiquen los valores en el libro 2. (destino)
    Gracias.

    ResponderBorrar
  89. El mejor consejo que puedo darte es rediseñar tu modelo. Creo que debe ser casi imposible de manejar y muy abierto a errores que pueden pasar desapercibidos.
    Mi sugerencia es que crees una tabla plana que haga las veces de base de datos y que crees los reportes que necesites a partir de esa base de datos, ya sea con tablas dinámicas o con fórmulas.

    ResponderBorrar
  90. Excelente detalle de explicación, de modo que quienes no tienen mucha experiencia pueden rápidamente generar vínculos.

    Por favor podrías indicar como se pueden romper los vínculos, ya entre a la ventana de modificar vínculos, le di al botón de romper vínculos y no sucede nada, existe otra forma de romper vínculos???

    ResponderBorrar
  91. Lo más probable es que existan nombres definidos que se refieren a rangos en cuadernos remotos. Fijate en la cinta de opciones en Fórmulas-Nombres definidos.

    ResponderBorrar
  92. Hola

    Tengo dos planillas vinculadas pero la informacion no se ve en una planilla si la otra no la tengo abierta, es decir que para que los datos se vinculen correctamente tengo que tener las dos planillas abiertas si no muestra el error #¡VALOR!
    Yo creo que es porque los archivos estan en distintos discos. Como puedo solucionar esto?

    ResponderBorrar
  93. Cande, el problema no está relacionado con los vínvulos sino con el hecho que ciertas funciones de Excel dan ese error cuando se refieren a cuadernos cerrados. Fijate en esta nota

    ResponderBorrar
  94. Hola, tengo una consulta, estoy tratando de crear una hoja de inventario exactamente igual a otra donde manejo los productos, ya explicaron como puedo pegar datos y me salgan en la otra pagina si modifico algo, pero como hago para agregar por ejemplo una celda en una pagina y que automaticamente se agregue en la otra??? Agradeceria inmensamente su ayuda.

    ResponderBorrar
  95. Por Haddie, ver lo que pongo en el enlace Ayuda, en la parte superior del blog.

    ResponderBorrar
  96. Hola tengo una pregunta sobre excel. nada que ver con este articulo pero no se si alguien pueda ayudarme lo agradeceria!
    Tengo una hoja de calculo (1). en ella van detalles de tipo A y B. necesitaria crear otras dos hojas de calculo (2) y (3) que en (2) este todo lo que sea A y en la (3) todo lo que sea B. jaja se entiende? muchas gracias si alguno pudiera ayudarme

    ResponderBorrar
  97. Sergio,
    efectivamente, no tiene nada que ver con el post. Además, no, no se entiende. Por favor, fijate lo que pongo en el enlace Ayuda (en la parte superior del blog).

    ResponderBorrar
  98. Hola Jeffers, como le comentaba a Joaquiín, no todas las funciones aceptan referencias a cuadernos cerrados. En lugar de SUMAR.SI te sugiero usar SUMAPRODUCTO. Fijate en este post.

    ResponderBorrar
  99. Buenas tardes. He leído este Blog dwetenidamente y me parece muy interesante. Me gustaría saber si me pueden resolver una duda. Tengo 4 libros de Excel de Cartera donde es alimentado por 4 personas diferentes, por lo tanto, cada libro está protegido con contraseña. Tengo un quinto libro tambien protegido por contraseña y el cual tiene 2 hojas, en la primera hoja es el resumen de los 4 libros anteriormente mencionados y la segunda es una tabla dinámica. Cuando abro el quinto libro, el me pide actualizar información, se actualiza y acto seguido me pide las contraseñas de los 4 primeros libros. Obviamente, la persona que maneja el quinto libro no las conoce. Como puedo hacer para que al abrir el quinto libro me traiga la información de los cuatro primeros libros y me tome automáticamente las contraseñas de los otros cuatro?.

    Agradezco de antemano su amable colaboración y quedo a la espera de sus comentarios.

    Cordialmente,

    Carlos R.

    ResponderBorrar
  100. Hola Carlos,

    bien, esa es la función de las contraseñas. Si no las sabes, no entras.
    La única forma de automatizar el proceso es usar Vba (macros). El código deberá contener las contraseñas necesarias.

    ResponderBorrar
  101. Hola Jorge,

    Gracias por tu artículo.

    Ahora mismo estoy con Outlook 2013 y no encuentro la manera de actualizar los datos automaticamente de un fichero externo cuando el fichero principal está abierto.

    Te comento:

    Tengo un fichero en red que está en: s:\sales\input.xlsx
    y otro fichero que está en otra carpeta s:\sales\day\actual.xlsx

    Debo mantener el fichero actual.xlsx siempre abierto y necesito que cuando se modifique la información en el fichero input.xlsx se actualicen los cambios en actual.

    La única manera que consigo que se actualizen los datos es abriendo y cerrando el fichero actual o actualizando los links manualmente en Data>Connections>Edit links> Update Values.

    Y la idea es mostrar este fichero en una pantalla mostrando los datos de ventas sin tener que actualizar los datos manualmente.

    Cómo puedo conseguir automatizar esta tarea?

    Espero tu ayuda Jorge,

    Saludos coordiales,

    Victor


    ResponderBorrar
  102. Supongo que te refieres a Excel 2013, no a Outlook. No describes como llegan los datos a la hoja Actual (BUSCARV? referencia?).
    Dependiendo del método (hay funciones que requieren que los dos cuadernos estén abiertos y hay otras queno, busca el post relacionado en mi blog), al actualizar los datos en la fuente hay que hacer "algo" para actualizarlos en Actual. Podría ser una macro (evento) en la fuente. También podrías, si utilizas Obtener Datos Excternos, definir en la venana de las propiedades "Actualizar cada xx minutos".

    ResponderBorrar
  103. Buenas tardes,
    Primero felicitarte por esta "biblia de excel". Espectacular !!!

    Tengo varios ficheros con una serie de hojas (empleado1...n) y en una celda aparece el puesto de trabajo, y tengo otro fichero que serviria de patron con una serie de hojas (puestos de trabajo)
    Necesito que al hacer el vinculo entre los dos ficheros el nombre de la hoja (ubicada en patron) la coja de una celda en una hoja del fichero de empleados en este caso.
    Es decir
    fichero:empleado->hoja:empleado1->celda:"administrativo"
    fichero:patron->hoja:administrativo.

    No se si me he explicado bien.

    Saludos y muchas gracias

    Jorge Alvarez

    ResponderBorrar
  104. Hola Jorge, se me había pasado por alto este comentario. Mis disculpas.
    Te sugiero que veas lo que pongo en el enlace "Ayuda" (en la parte superior del blog) y te pongas en contacto conmigo por mail privado.

    ResponderBorrar
  105. Hola jorge,
    Te comento el inconveniente que estoy teniendo.
    actualmente en el laburo diseñe un archivo utilizando la formula buscarv, este archivo obtiene informacion desde otro libro de excel que esta protegido con una contreseña, todos los dias lo tengo que abrir para que se actualice la informacion y esta se vea reflejado en el archivo que diseñe actualmente.Existe alguna macro para que de manera automatica se abra el excel desde donde saco la informacion y obviamente que no me pida la contraseña(se la contraseña, pero me gustaria que no me la pida)?
    Espero tu respuesta, desde ya muchas gracias.
    Saludos

    ResponderBorrar
  106. Si, por supuesto, pero las macros no existen por si mismas (como las funciones) sino que hay que programarlas.
    Te sugiero que veas la posibilidad de usar Power Query en lugar de macros, que será mucho más eficiente.

    ResponderBorrar
  107. Hola, buenas tardes
    Me podría ayudar en lo siguiente:
    Tengo un presupuesto en una hoja de excel esta tienen (numero de item, descripción valor unitario y valor total) a cada item debo hacerle un análisis de precios unitarios en diferente hoja donde debe ir también la información de numero de item, descripción valor unitario hay alguna función para cuando yo digite puede ser el numero del item automáticamente me aparezca la otra información

    ResponderBorrar
  108. Te sugiero que busques informacion (en este blog y en la Web en general) sobre las funciones BUSCARV e INDICE y tambien sobre Tablas Dinamicas.

    ResponderBorrar