7

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:

referencias externas excel

¿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:

referencias externas excel direccion libro

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.

buscar referencias externas excel

  1. 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.
  2. Pulsa en Buscar todo.
  3. 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:

  1. Pulsa en la ficha Datos.
  2. 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.
  3. Pulsa en el botón Romper vínculo. Esto convertirá directamente las fórmulas en sus valores calculados.

romper vinculos externos excel

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!

¡Compartir es vivir!
Sergio
 

La destreza y el perfeccionismo quizá sean las dos virtudes que me permiten ayudar a mis clientes a facilitar las tareas administrativas de sus negocios.

Haz clic aquí para dejar un comentario 7 comentarios
Oscar G. Peinado - 03/04/2017

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

Responder
    Sergio - 03/04/2017

    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.

    Responder
Oscar G. Peinado - 03/04/2017

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.

Responder
    Sergio - 03/04/2017

    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.

    Responder
Javier - 04/04/2017

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.

Responder
    Sergio - 04/04/2017

    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.

    Responder
Darwin Arevalo - 20/10/2017

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!

Responder

Escribe una respuesta: