Error con referencias externas – Cómo solucionarlo
Hace poco recibí un libro de Excel de un alumno para que le echase un vistazo con un problema que le había surgido con las referencias externas que contenía. Al abrirlo me apareció este mensaje que me recordó cuando lo ví por primera vez. Aunque con otras palabras, tenía un aspecto similar a este:
¿Por qué me aparece esto?, me preguntaba. Como alguna vez más he recibido este tipo de consulta, he escrito este artículo para que lo puedas solucionar tú mismo:
¿Qué es un vínculo o referencia externa?
Cuando creas una fórmula que hace referencia a otro libro, Excel enlaza la fórmula directamente al libro. De esta forma, la fórmula podría ser algo parecido a esto:
Observa cómo he señalado en amarillo la parte de la fórmula que contiene el enlace externo (o referencia externa). Esto le dice a Excel que la función BUSCARV debe ir al libro “Puntuación general” a buscar los resultados y devolver el valor que corresponda.
La ventaja principal de las referencias externas es que al modificar los datos del libro vinculado, los resultados se actualizan automáticamente.
Mientras no se cambie de ubicación el libro que contiene la fórmula, todo funcionará correctamente, pero… ¿qué ocurre si envías el libro por correo a otra persona o lo cambias de carpeta en tu equipo?
Al intentar abrir el libro con las referencias externas te aparecerá el error de la imagen de arriba.
Para evitar este problema te recomiendo eliminar estas referencias. Cuando el libro tiene pocas fórmulas te resultará fácil hacer las modificaciones oportunas, pero cuando se trata de un libro más complejo con cientos o miles de fórmulas, te podrías volver loco.
¿Cómo buscar las referencias externas en Excel?
Te voy a mostrar un par de formas de localizar estas referencias:
- Utilizando la herramienta Buscar.
- Utilizando la herramienta Editar vínculos.
Te las muestro a continuación:
Cómo buscar referencias externas con la herramienta Buscar
Todas las celdas con referencias externas contienen el nombre del libro al que enlazan. Esto significa que la celda siempre tendrá en su fórmula la extensión del libro (.xlsx, .xlsm, .xlsb, etc.). Esto se puede aprovechar para encontrar las celdas.
- En la ficha Inicio, pulsa en el botón Buscar y Seleccionar y, a continuación, en Buscar.
En el campo de texto introduce el texto *.xl*. Una forma más rápida es utilizar este atajo de teclado. - Pulsa en Buscar todo.
- En el cuadro que aparece debajo se mostrarán todas las celdas que contienen referencias externas.
Una vez encontradas las celdas, puedes convertir las fórmulas en valores.
Cómo buscar referencias externas con la herramienta Modificar vínculos
Excel también cuenta con una herramienta que te ayudará a encontrar referencias externas en tus libros. Sigue los siguientes pasos:
- Pulsa en la ficha Datos.
- En el grupo Conexiones haz clic en el botón Modificar vínculos. Esto abrirá el cuadro de diálogo Modificar vínculos donde encontrarás todas las referencias externas del libro.
- Pulsa en el botón Romper vínculo. Esto convertirá directamente las fórmulas en sus valores calculados.
Si utilizas este método debes tener cuidado. La acción de romper los vínculos no se puede deshacer.
¿Todavía aparece el error?
Si después de seguir los pasos anteriores se sigue mostrando el mismo error, no te preocupes. Puedes seguir buscando referencias externas en estos sitios:
- Nombres de rangos: En la ficha Fórmulas haz clic en el botón Administrador de nombres. En el cuadro se mostrarán todos los nombres con rango que contiene el libro. Revisa cada uno de estos nombres, sobre todo el campo Se refiere a.
- Formato condicional: Otro lugar donde se pueden esconder las referencias externas es en las fórmulas de los formatos condicionales. En la ficha Inicio haz clic en el botón Formato condicional y, a continuación en Administrar reglas. Comprueba las fórmulas de cada una de las reglas.
- Validación de datos: La herramienta Validación de datos también puede contener referencias externas. Repasa los criterios de validación, sobre todo las listas y los criterios personalizados.
- Formas. Si estás utilizando formas o SmartArt, comprueba que no contienen referencias externas:
Pulsa la tecla F5 para abrir el cuadro de diálogo Ir a.
Haz clic en el botón Especial.
Selecciona la opción Objetos.
Una vez seleccionados puedes desplazarte entre ellos pulsando la tecla Tab para comprobar si contienen alguna referencia.
- Títulos de gráficos: Selecciona el título del gráfico y comprueba que en la barra de fórmulas no aparecen referencias externas.
Resumiendo
Si trabajas con libros que contienen referencias a otros libros, te recomiendo que antes de compartirlo, conviertas estas referencias a valores. De esta forma evitarás que aparezcan errores en los archivos.
Si eres tú el que recibes este tipo de archivos, también puedes seguir estos pasos para que deje de aparecer este molesto error.
Personalmente te recomiendo la segunda opción para convertir las referencias externas en valores. Es más rápida y además te aseguras de que todas las fórmulas quedan actualizadas.
¿Te ha ayudado este artículo? ¡Coméntalo!
Buena recopilación.
Lo malo es que cuando Excel da el aviso dice que hay error en “alguna referencia” pero no en cual. Manejo libros con decenas de referencias y cuando falla algo es trabajo de chinos ir buscando cual es el que falla.
Y lo mismo pasa cuando vinculo un PPT a decenas de gráficos Excel y no hay manera de identificar a que gráfico corresponde cada vínculo.
¿No tendrás algún truco escondido para eso? XD
Oscar, con la primera opción puedes encontrar en qué celda se encuentra el enlace con la referencia. Una vez te dirijas a la ubicación, podrás ver en la barra de fórmulas a qué archivo está vinculada la fórmula.
Ya, eso sí. El problema es que cuando falla una referencia y hay insertadas 100, de media habrá que revisar 50 una a una, hasta encontrar la que falla, aunque la búsqueda ayude es un poco tedioso.
Haría falta que ante esos errores Excel indicara en que hoja y celda está dando el error, e incluso en los otros recursos que has comentado, que yo hasta que me di cuenta que podía estar en los nombres de rangos me volví un poco loco buscando.
Tienes razón. Si de 100 referencias hay una que falla, te vuelves loco para encontrarla.
Si eso te pasa a menudo te recomiendo que instales algún complemento con el que puedas auditar las fórmulas. Te detectará todos los errores del libro.
Buen aporte Sergio, gracias.
Te comento un error que me pasa a mi relacionado con las referencias externas:
Tengo un documento que utilizo como base de datos y otro donde hago diversos informes relacionados con esa base de datos, si abro a la vez los dos documentos, las referencias a la base de datos se me rompen, me cambia la matriz_buscar_en de la función BuscarV por #!REF!
Es decir:
BUSCARV(D10;’RUTA\[NOMBRE_DOCUMENTO.xls]HOJA’!$A:$T;19;FALSO)
BUSCARV(D10;[NOMBRE_DOCUMENTO.xls]HOJA!#¡REF!;19;FALSO)
Muchas gracias por tu ayuda.
Javier, eso sucede casi siempre porque el libro hace referencia a celdas que no existen, como filas negativas o columnas anteriores a la A.
Se suele dar cuando creas una fórmula con una referencia a una celda y copias esa fórmula hacia arriba o hacia la izquierda, de forma que las referencias también se mueven hasta que ya no quedan celdas.
La solución sería crear una fórmula lo suficientemente consistente (alternando referencias absolutas y relativas) que te permita copiarla para que las referencias se mantengan.
Hola Sergio, la verdad es que tengo una formula bien consistente, con referencias relativas y absolutas, pero me sucede lo mismo, el problema aparece cuando cierro los libros luego de trabajar y al otro día cada usuario abre su archivo y sucede que se rompe la referencia, no hay nada copiado a la izquierda ni hacia arriba, solo pasa al abrir los 2 archivos juntos, incluso sucede con una referencia simple, es decir; +’N:\Programa\[Programa autobox v3.xlsm]#¡REF’!$A$11
(no hay calculo ni nada, solo debería traer el valor del otro libro.
¡Hola, Rodrigo! Por favor, échale un vistazo a este enlace. Le da solución al problema. Comenta si te ha servido.
Me pasas ese link?
Creo q eso me pas a…trabajo con un excel en red que tiene vinculos y luego q trabaja alguien no puedo modifcarlo y muestra una direccion diferente….x eso no se muestra el vinculo…Ej: las q modifirco yo me las muestra con la unidad de red mapeada +M:\…. ylas q no me deja modificar q han modificado otros compañeros me las muestra con la ruta del servidor \\adm\documentos….
Tu que piensas como puedo solucionar eso…se estan rompiendo siempre los vinculos cuando modifica uno u otro.
Saludos amigo!
Este es el enlace:
https://support.microsoft.com/es-es/office/controlar-cu%C3%A1ndo-se-actualizan-las-referencias-externas-v%C3%ADnculos-21e995b5-bab1-4328-8ab3-dd357fe0e653
Hey! Muchas gracias… tenía una referencia externa que no podía eliminar …. en “Romper vinculos” .. y ahora que leí tu contenido también tenía esa referencia en validacion de datos 😀 Muchas gracias.. Ya mero quería borrar el archivo y volver a hacerlo 🙂 me has salvado!
Tengo este problema en excel. cuando abro el archivo me tira que no puede encontrar el archivo xxx0123.xlsm pero no tengo ninguna formula haciendo referencia al mismo. ya hice todos los pasos de sugeridos y sigue ahí el error. No se donde mas buscar. Ayuda!!! Gracias.
Diego, no te limites sólo a las fórmulas de hoja. Búscalas también en validaciones de datos, formatos condicionales y, sobre todo, en nombres de rango, que es donde suelen “esconderse” las fórmulas.
Hola Sergio,
nos encontramos con ese mismo error con una hoja que está en red.
Tenemos varias hojas vinculadas a otra.
Todas la hojas están en una unidad de red externa.
Todos podemos actualizar la hoja de destino, menos la persona que gestiona la hoja de origen.
Si gestiona los datos en la hoja de origen y seguidamente intenta actualizar la de destino, esta no se actualiza. Para que funcione lo tiene que hacer otro usuario desde otro pc donde no le aparece este error.
De que depende? non conseguimos arreglar esta incidencia.
Gracias
Laura
Laura, debéis prestar especial atención a las letras de las unidades de los equipos.
Puede que la misma ubicación de red en tu equipo se llame H: y en el equipo de otra persona se llame I:. Como las referencias que se guardan en otros equipos son absolutas (la ruta completa) por eso en un equipo da error y en los otros no.
Haz esta comprobación a ver si se soluciona.
Buenas tardes Sergio,
tengo una hoja de excell, con vínculos externos. Dichos vínculos, no los encuentra (la hoja se perdió). Sin embarco, realiza bien el cálculo. Como es posible???
Hola, José Luis:
Cuando abres el libro de Excel, éste contiene los últimos datos que tenía cuando se guardó antes de cerrarlo.
Cuando el archivo se abre nuevamente, se comprueban los vínculos antes de actualizarlos y, si no encuentra las referencias, se detiene.
Hola Sergio,
es una hoja de calculo de cables. En mi hoja (destino) cambio los datos. en función de esos datos, va a buscar una tabla a la hoja origen. Esa hoja, ya no existe. Sin embargo, escoge bien de la tabla origen. Vamos, que mi hoja destino, funciona perfectamente. La verdad, no me lo explico…
Muchas gracias
Mil gracias Sergio.
Tenía una referencia externa que no localizaba, y en mi caso era un formato condicional.
Quitado, luego rotos los vínculos y se acabó el problema.
Muchas gracias
existe alguna forma de quitar una parte de la ruta del archivo si el archivo a utilizar en la búsqueda se encuentra en una misma carpeta? saludos desde Colombia.
Hola Sergio.
Tengo un problema. Tengo un libro destino este rompe o pierde el vinculo al abrir el libro origen. solo al abrirlo, ósea que no puedo tener ambos abiertos ya antes si podía. Como puedo hacer para poder trabajar con ambos abiertos.?
Saludos
Excelente, son datos muy útiles. Saludos
Hola, te hago la siguiente consulta: Una vez creada una conexion, habra manera de acceder a esta desde una formula? o sea sin importar los datos a excel. por ej una formula que sume un campo.
Excelente aporte!!!!
Sin más palabras, es el compilado más completo que encontré.
Gracias!!!
Muchas gracias Sergio, querido tocayo. Primera vez que escribo. Pero en realidad desde hace mucho que me ayudas con los grandes “pequeños” problemas del Excel.
Un gran abrazo!!
¡Me alegra saberlo tocayo!