El Total del Grupo Decía #¡REF! a las Nueve, Decía un Número a las Diez, y Se Quedó 111.237,15 Corto el Resto del Año

El libro se llama Maintenance_2026_08.xlsx. Veinte delegaciones, una hoja por delegación, y una pestaña Resumen al principio que lee la cifra de cierre de cada delegación desde la hoja de la propia delegación. Es la forma que tiene la mitad de los libros de finanzas del mundo, y lleva funcionando desde que la contrata tenía seis delegaciones.

Todas las celdas de la columna de cifras del Resumen tienen la misma fórmula, arrastrada hacia abajo:

=INDIRECTO("'"&A2&"'!B18")

La columna A tiene el nombre de la delegación. B18 es donde está el total general en una hoja de delegación. El lunes en que vencía el cierre de agosto, la columna estaba así:

A las diez y media los cinco errores habían desaparecido y el total del grupo era un número: 582.571,90. La verdad era 693.809,05. Nadie encontró los 111.237,15 que faltaban hasta el marzo siguiente.

Nada en el libro lo había roto una fórmula. Cada una de las veinte hojas de delegación cuadraba — la SUMA de cada delegación se había expandido sobre cada fila insertada exactamente como Excel promete. Las veinte cosas que estaban rotas eran veinte cadenas de texto, y una cadena es la única parte de una fórmula que Excel no va a mantener nunca en tu nombre.

Qué cubre esto. Todo lo de aquí se comporta igual en Excel 2016, 2019, 2021, 2024, Microsoft 365 y Excel para Mac, con las excepciones que se señalan donde aparecen: BUSCARX y LET necesitan 365 o 2021 en adelante, y TEXTODESPUES necesita 365 o 2024. INDIRECTO, DESREF, INDICE, COINCIDIR, SUMA, SUMAR.SI, SUMAR.SI.CONJUNTO, CONTAR, CONTARA, CONTAR.SI, CONTAR.SI.CONJUNTO, SUMAPRODUCTO, SI, SI.ERROR, ESREF, ESNUMERO, NOD, FILA, COLUMNA, COLUMNAS, DIRECCION, CELDA, LARGO y ESPACIOS funcionan en todas partes. El ejemplo es una consolidación de una hoja por delegación porque es el sitio donde más se recurre a INDIRECTO, pero el trabajo es el mismo que en un cuadro de mando que cambia de hoja de origen con una lista desplegable, una fila de doce meses móviles construida con DESREF, una lista desplegable dependiente, o cualquier fórmula que monte una referencia a partir de texto.

1) Veinte Fórmulas, Una Columna, Tres Desenlaces

Las veinte fórmulas son idénticas. Se diferencian solo en el nombre de delegación que concatenan, y salieron de tres maneras distintas.

Lee esa última fila dos veces. El fallo que cubría el 62,99% del fichero es el que no produjo ningún error, ningún aviso, ningún color y ninguna marca — y es el que sobrevivió, porque lo único que una hoja de cálculo escala de forma fiable es un valor de error.

🎯 Escenario: Cuando una consolidación se construye sobre INDIRECTO, la pregunta útil nunca es «¿calcula el resumen?». Calcula siempre. La pregunta es «¿cómo se vería esta columna si estuviera mal?». Si la respuesta es «exactamente así», la columna no es prueba de nada.

2) Qué Hace Realmente INDIRECTO

INDIRECTO recibe texto y devuelve la referencia que ese texto describe. Eso es toda la función. =INDIRECTO("B18") devuelve lo que haya en B18, y =B18 también, y por un momento parecen dos formas de escribir lo mismo.

No lo son, y esa diferencia es el artículo completo.

Cuando escribes =B18, Excel no guarda los caracteres B, 1 y 8. Guarda una relación — esta celda depende de esa celda — y mantiene esa relación el resto de la vida del libro. Inserta una fila encima de la 18 y la fórmula pasa a ser =B19 ella sola. Renombra la hoja y todas las referencias a ella se reescriben en todas las fórmulas del libro, en silencio y correctamente, incluidas las de otras hojas y otros libros abiertos. Corta B18 y pégala cuatro columnas a la derecha y la fórmula la sigue. Nada de eso lo pediste. Es el servicio que estás pagando cuando usas una referencia.

Cuando escribes =INDIRECTO("B18"), Excel guarda los tres caracteres B18 como un trozo de texto y los evalúa, de nuevo, en cada recálculo. No hay relación que mantener, porque no hay relación: hay una frase acerca de una relación. Inserta una fila encima de la 18 y la frase sigue diciendo B18. Renombra la hoja y la frase sigue nombrando la hoja antigua. Mueve la celda y la frase se queda donde está, apuntando a lo que haya ocupado el hueco.

Una referencia es una relación que Excel mantiene. Un INDIRECTO es una afirmación sobre una distribución, y a nadie le toca mantener una afirmación.

Eso no es un defecto. Es el sentido de la función — usas INDIRECTO precisamente cuando quieres que la referencia se decida en tiempo de cálculo a partir de algo que varía, normalmente una celda que elige el usuario. Lo que sale mal no es la función. Lo que sale mal es que una fórmula construida así ha trasladado en silencio el trabajo de mantener la referencia verdadera de Excel a ti, y no te avisa de que lo ha hecho.

🎯 Escenario: Antes de escribir un INDIRECTO, termina esta frase en voz alta: «esto seguirá bien salvo que alguien ______». Si el hueco se puede rellenar con «renombre una pestaña», «inserte una fila», «ordene la hoja» o «ponga orden», acabas de escribir una fórmula con un contrato de mantenimiento adjunto y sin nadie que lo mantenga.

3) El Renombrado Que Falla a Gritos

Cinco pestañas se renombraron entre enero y agosto. Nadie hizo nada irrazonable:

Cada una de esas cinco produjo #¡REF! en el Resumen, y #¡REF! es exactamente lo correcto: el texto 'Chesterfield'!B18 describe una referencia que no existe, y una referencia que no existe es lo que significa #¡REF!. No #¿NOMBRE? — el nombre de la función está bien escrito y el argumento es una cadena válida; lo que pasa es que la cadena no apunta a ninguna parte.

Aquí está la parte que merece un momento. Un renombrado es el tipo de cambio que Excel absorbe mejor. Renombra una pestaña y todas las referencias reales a ella, en cualquier parte del libro, se reescriben por ti antes de que hayas soltado el ratón. Las cinco personas que renombraron esas pestañas tenían todos los motivos para creer que Excel se había encargado, porque en todas las demás fórmulas del edificio, Excel se había encargado.

🎯 Escenario: Si los nombres de hoja de un libro son estructurales — si alguna fórmula en algún sitio escribe uno entre comillas — entonces renombrar una pestaña es un cambio de código y hay que tratarlo como tal. La versión práctica de esa regla: no escribas nunca un nombre de hoja dentro de una fórmula. Ponlo en una celda, en una lista, una sola vez, donde se pueda ver y corregir en un único sitio.

4) El Espacio Final Que Nadie Puede Ver

Tiverton merece su propia sección, porque es el único fallo de este artículo que ningún cuidado en el teclado habría evitado.

La pestaña se llama Tiverton . Un espacio, al final. En la pestaña es invisible — la pestaña es un poco más ancha de lo que Tiverton necesita y eso es todo. En el Cuadro de nombres es invisible. En una captura de pantalla es invisible. Y 'Tiverton'!B18 no es 'Tiverton '!B18, así que la referencia falla del todo.

También es de origen completamente ordinario: alguien hizo doble clic en la pestaña, escribió el nombre, y el pulgar le pilló la barra espaciadora antes del Intro.

Dos fórmulas lo hacen visible. En la propia hoja de la delegación:

=TEXTODESPUES(CELDA("filename";A1);"]")          → "Tiverton "
=LARGO(TEXTODESPUES(CELDA("filename";A1);"]"))   → 9

Tiverton tiene ocho caracteres. Un nueve te cuenta la historia entera. (CELDA("filename") necesita un fichero guardado, y TEXTODESPUES es de 365 y 2024; en versiones anteriores usa =EXTRAE(CELDA("filename";A1);ENCONTRAR("]";CELDA("filename";A1))+1;255).)

Y en el Resumen, la prueba que hace la pregunta directamente en vez de deducirla de un total:

=ESREF(INDIRECTO("'"&$A2&"'!A1"))                → VERDADERO en 15 filas, FALSO en 5

ESREF es la herramienta adecuada aquí y casi nunca se recurre a ella. Responde a «¿es esta una referencia real?» sin importarle lo que contenga la celda, así que separa la hoja no está de la hoja está y el número es raro — los dos problemas que tenía este libro, y que cualquier otra comprobación confunde entre sí.

🎯 Escenario: Pon =ESREF(INDIRECTO("'"&$A2&"'!A1")) en una columna auxiliar al lado de cualquier lista de nombres de hoja de la que un libro se gobierne, y =CONTAR.SI(C2:C21;FALSO) encima. Responde en un solo número si el mapa que el libro tiene de sí mismo sigue siendo exacto. Aquí habría dicho 5 la primera mañana, y 5 es indiscutible.

5) La Fila Insertada Que No Falla Nunca

Ahora las ocho que importaban.

Una hoja de delegación es algo sencillo: etiquetas por la columna A, dinero por la columna B, categorías subtotalizadas, total general al final. Tal como se construyó, todas las hojas de delegación tenían el subtotal de mano de obra en la fila 16 y el total general en la 18, que es por lo que el Resumen dice B18 veinte veces.

Durante el año, ocho delegaciones empezaron a alquilar maquinaria. Alguien — normalmente el administrativo de la delegación, haciendo exactamente lo correcto — insertó un par de filas y un bloque nuevo encima del total. Seis hojas ganaron una fila, dos ganaron dos. El total general se mudó a la fila 19 o a la 20.

La hoja de la delegación seguía correcta. Esta es la parte que hace el fallo tan difícil de ver: la fórmula del total era =SUMA(B2:B17), las filas se insertaron dentro de ese rango, y Excel lo expandió a =SUMA(B2:B18) automáticamente. La hoja de la delegación cuadraba al céntimo. Siempre lo había hecho.

Solo el Resumen estaba mal, y estaba mal de la peor forma posible: B18 en esas hojas es ahora el subtotal de mano de obra.

Cada uno de esos ocho números es real. Salió de la hoja correcta, de la columna correcta, en la divisa correcta, del mes correcto, y es del orden de magnitud correcto — cinco de los ocho están entre el 81% y el 84% de la cifra que sustituyeron, es decir, están mal más o menos por el tamaño de una factura de maquinaria. No hay ninguna propiedad de ninguna de esas celdas que sea inusual. No hay pista de formato, ni error, ni desajuste de texto contra número, ni negativos, ni ceros, ni valores atípicos. Una comprobación de rango pasa. Una de signo pasa. Una de «¿tiene pinta de estar bien?» pasa con entusiasmo, porque sí tiene pinta de estar bien.

🎯 Escenario: Una referencia que apunta a la fila equivocada es mucho más peligrosa que una que no apunta a nada, y la diferencia no es matizada: es la diferencia entre un fallo que arreglas antes de comer y un fallo que se envía cada mes durante siete meses. Cuando direccionas una celda por sus coordenadas, estás afirmando que ningún ser humano insertará nunca una fila encima. Esa afirmación no se ha cumplido jamás.

6) Por Qué Rastrear Precedentes No Te Muestra Nada

Hay una segunda razón por la que las ocho sobrevivieron, y no va de números. Todas las herramientas que Excel te da para auditar una fórmula son ciegas ante una dirección entre comillas.

• Rastrear precedentes dibuja flechas por el árbol de dependencias. Un INDIRECTO no tiene precedente que rastrear más allá de la celda con el nombre, así que la flecha se detiene en A2 y la hoja de la delegación no aparece nunca. El dibujo no está mal; está vacío exactamente en el sitio donde estás mirando.
• Ctrl+[ (Ir al precedente) no tiene adónde ir y no hace nada.
• Buscar con B18 encuentra estas fórmulas, pero Buscar con lo que de verdad importa — qué celdas dependen de esta hoja — no te puede ayudar, porque la dependencia no está registrada.
• La corrección automática del renombrado que reescribe todas las referencias cuando renombras una pestaña no puede reescribir texto. No sabe que tu texto es un nombre de hoja. Para Excel es una cadena, como "Total" o "kg".
• Fórmulas ▸ Evaluar fórmula sí lo recorre paso a paso, y es la única herramienta que funciona. Es también la única que nadie abre en una fórmula que no está dando error.
• El árbol de dependencias no contiene el vínculo en absoluto, que es por lo que INDIRECTO es volátil: Excel no puede saber cuándo cambiaría la respuesta, así que la recalcula siempre. Más sobre ese coste en el §12.

Así que el libro contenía veinte vínculos a veinte hojas, sin documentar y sin rastrear, y el único sitio donde estaban escritos era dentro de las propias cadenas.

🎯 Escenario: Si heredas un libro, haz Ctrl+B buscando INDIRECTO( y DESREF( con Buscar dentro de ▸ Fórmulas y Dentro de ▸ Libro antes de cambiar nada estructural. Esa búsqueda es la lista de las suposiciones que el fichero hace sobre su propia forma — y es la única lista que existe.

7) El SI.ERROR Que Convirtió una Pregunta en una Respuesta

Volvamos al lunes por la mañana. =SUMA(B2:B21) devolvía #¡REF!, porque SUMA propaga los errores, y un total de grupo que dice #¡REF! no se le puede mandar a nadie.

El primer impulso fue el de siempre:

=SI.ERROR(INDIRECTO("'"&A2&"'!B18");0)

Y el total pasó a ser un número: 411.944,40, frente a una verdad de 693.809,05 — el 59,37% de ella. Un número que está un 40% mal, en una página, sin ninguna señal de nada.

Luego alguien sensato miró qué filas se habían puesto a cero, las cruzó con las pestañas renombradas, corrigió los cinco nombres de la columna A, y el total se movió a 582.571,90 — el 83,97% de la verdad. En ese punto todas las celdas de la columna tenían un número plausible, los cinco fallos ruidosos no le habían costado nada a la empresa, y los ocho silenciosos le costaban 111.237,15 al mes.

Merece la pena ser preciso con el SI.ERROR, porque no es una función equivocada — es una función correcta apuntando a lo equivocado. SI.ERROR(x; 0) dice si esto no se puede calcular, la respuesta es cero. Eso es cierto y útil cuando una búsqueda en blanco significa de verdad que no se vendió nada. Es mentira cuando el motivo por el que la celda dio error es que el libro ya no sabe dónde mirar, porque en ese caso la respuesta honesta no es cero, es «no lo sé» — y Excel tiene un valor exactamente para eso:

=SI.ERROR(INDIRECTO("'"&A2&"'!B18");NOD())

NOD() se propaga. SUMA sobre una columna que contiene #N/D devuelve #N/D, los gráficos se lo saltan en vez de dibujarlo como un cero, y ningún total sale del edificio cargando un agujero que no menciona. Un cero es una respuesta. #N/D es una pregunta, y toda la razón por la que las ocho duraron siete meses es que a este libro le habían enseñado a dejar de preguntar.

🎯 Escenario: La prueba para cualquier SI.ERROR que estés a punto de escribir es una frase: ¿es cierto el valor alternativo? Si el alternativo es «cero» y el cero es una respuesta realmente posible, déjalo. Si el error significa que la fórmula ha perdido el pie, el alternativo es NOD(), y un total que se niega a sumar es la característica que estás comprando.

8) Lee la Etiqueta, No Solo el Número

Aquí está el arreglo más barato del artículo, y habría cazado los dos fallos la primera mañana.

Toda hoja de delegación tiene una etiqueta en la columna A al lado del dinero de la columna B. La fila 18 era el total general, así que A18 decía Total. Trae eso también:

=INDIRECTO("'"&$A2&"'!A18")

Arrastrada veinte filas, esa columna devuelve Total siete veces, Labour subtotal ocho veces, y #¡REF! cinco veces. 7 de 20. Una fórmula, sin análisis, y la respuesta no es un número sobre el que tengas que opinar — es una palabra que es o no es la palabra que esperabas.

Después haz que la propia cifra dependa de ella:

=SI(INDIRECTO("'"&$A2&"'!A18")="Total";INDIRECTO("'"&$A2&"'!B18");NOD())

Ahora una delegación a la que le han insertado una fila reporta #N/D en vez de su subtotal de mano de obra, el total del grupo reporta #N/D en vez de 582.571,90, y alguien tiene que ir a mirar. Trece filas habrían fallado a gritos la primera mañana en vez de cinco, y trece es el número correcto.

O con LET, que evalúa el prefijo de hoja una vez en lugar de tres:

=LET(hoja; "'"&$A2&"'!";
     etiqueta; INDIRECTO(hoja&"A18");
     SI(etiqueta="Total"; INDIRECTO(hoja&"B18"); NOD()))

🎯 Escenario: Cualquier fórmula que meta la mano en una distribución debería llevar al lado una segunda fórmula que compruebe que la distribución sigue siendo la que se escribió. El número te dice qué contiene la celda; la etiqueta te dice si la celda es la celda que querías. La etiqueta es la única de las dos que puede cazar una fila insertada, y cuesta una columna.

9) Deja de Direccionar la Fila. Búscala.

La comprobación de la etiqueta convierte un fallo silencioso en uno ruidoso, que es una mejora grande y aún no es el arreglo. El arreglo es dejar de fijar un número de fila: no digas el total está en la fila 18, di el total es la fila que dice Total.

=INDICE(INDIRECTO("'"&$A2&"'!B:B"); COINCIDIR("Total"; INDIRECTO("'"&$A2&"'!A:A"); 0))

O, desde 2021:

=BUSCARX("Total"; INDIRECTO("'"&$A2&"'!A:A"); INDIRECTO("'"&$A2&"'!B:B"); NOD())

Esto sobrevive a cualquier inserción, borrado y reordenación en las hojas de delegación. Los ocho fallos silenciosos habrían devuelto el número correcto automáticamente, la primera mañana y todas las siguientes, sin avisar a nadie y sin nada que arreglar — porque la fórmula ahora busca una cosa en vez de un sitio, y la cosa no se movió.

Fíjate bien en lo que queda. INDIRECTO sigue ahí, y tiene que estar: el nombre de hoja varía de verdad por fila y ese es el único trabajo que solo INDIRECTO puede hacer dentro de una fórmula. Los cinco renombrados seguirían dando error. Pero ese siempre fue el modo de fallo que funcionaba bien.

Dos refinamientos que valen las pulsaciones:

• Busca la etiqueta exacta y hazla única. Si una hoja tiene "Total" en A18 y "Total mano de obra" en A16, COINCIDIR("Total";…;0) va bien — es coincidencia exacta, no comodín. Pero si dos filas dicen exactamente Total, COINCIDIR se queda con la primera, que puede no ser la última. Etiqueta el total general con algo que aparezca una sola vez: Total delegación.
• Mejor un nombre definido en cada hoja. Si cada hoja de delegación tiene un nombre de ámbito de hoja TotalDelegacion sobre su celda de total, el Resumen pasa a ser =INDIRECTO("'"&$A2&"'!TotalDelegacion") — y un nombre es una referencia que Excel mantiene, así que sigue a la celda a través de inserciones, borrados y cortes. Sigues expuesto a que se renombre la pestaña, y a que se borre el nombre, pero el número de fila ha desaparecido del todo de la fórmula, que es lo que elimina el fallo que costó 111.237,15.

🎯 Escenario: La regla general que sale de todo este fichero: direcciona los datos por lo que son, no por dónde están. Un número de fila es un hecho sobre la distribución de hoy. Una etiqueta, un nombre o una columna de tabla es un hecho sobre los datos. Uno de los dos sobrevive a que un compañero sea servicial y el otro no.

10) DESREF Es el Mismo Trato con Otras Palabras

DESREF se recomienda como la alternativa sin texto a INDIRECTO, y sí evita la cadena. No evita el problema, porque el problema nunca fue la cadena — era fijar una posición que un humano puede mover.

DESREF(ref; filas; columnas; [alto]; [ancho]) parte de una referencia y camina un número fijo de filas y columnas desde ella. El ancla es una referencia de verdad, así que Excel la mantiene. Los desplazamientos son números, y Excel no mantiene números.

La fila de doce meses móviles del Resumen se construyó así:

=DESREF($B$4; 0; MES($B$1)-1)

B4 es enero, doce meses corren hacia la derecha, y la fórmula se desplaza al mes actual. Luego se insertó una columna para una categoría de coste nueva entre B y C. $B$4 pasó a ser $C$4 automáticamente — Excel mantuvo el ancla a la perfección, exactamente como se anuncia. El MES()-1 siguió siendo MES()-1, porque es aritmética y no hay nada en ella que mantener. La fórmula se desplaza ahora ocho columnas desde un punto de partida equivocado y lee la celda de septiembre para agosto, y seguirá haciéndolo hasta que alguien note que la fila del acumulado no cuadra con la columna del acumulado.

La comparación, expuesta sin rodeos:

INDICE es el sustituto honesto de DESREF en casi todos los casos, y no es volátil:

=DESREF($B$4; 0; MES($B$1)-1)      → volátil, anclado, se rompe al insertar
=INDICE($B$4:$M$4; MES($B$1))      → no volátil, y el rango se mueve con la hoja

La segunda tampoco necesita el truco de la primera columna: INDICE empieza en 1 sobre el rango que le pasas, así que el mes 8 es el argumento 8, que es una cosa menos que puedes equivocar frente al paso desde cero de DESREF.

🎯 Escenario: Si recurres a DESREF para sacar el enésimo elemento de una fila o una columna, INDICE lo hace, lo hace sin volatilidad, y lo hace con un rango que Excel mantiene verdadero. Guarda DESREF para lo único que INDICE no puede hacer sin ayuda: devolver un bloque de un alto y un ancho dados cuyo tamaño se calcula.

11) Dónde INDIRECTO Sí Se Gana Su Sitio

Nada de esto hace de INDIRECTO una mala función. Hace una cosa que no hace nada más, y hay cuatro trabajos en los que es la respuesta correcta.

1. Una referencia que elige el usuario. Un cuadro de mando con un desplegable de nombres de hoja, o un selector de región que decide qué rango con nombre se suma:

=SUMA(INDIRECTO($B$1))         donde B1 es un desplegable de "Norte", "Sur", "Este"

Esto es INDIRECTO haciendo exactamente aquello para lo que existe. El texto que evalúa vive en una celda, la celda está validada contra una lista, y la lista se mantiene en un solo sitio — que es toda la diferencia entre esto y escribir nombres de hoja dentro de veinte fórmulas.

2. Listas desplegables dependientes. La Validación de datos no acepta una fórmula que se derrame, pero sí acepta =INDIRECTO($A2), así que una celda de Categoría con Tornillería hace que la lista de Artículo resuelva al rango con nombre Tornillería. Sigue siendo el desplegable de dos niveles estándar en todas las versiones de Excel, y no hay forma más simple de hacerlo.

3. Congelar un rango a propósito frente a las inserciones. Este es el único caso en el que la ceguera de INDIRECTO es la característica:

=SUMA(INDIRECTO("B2:B100"))

Ese rango será B2:B100 para siempre. Inserta filas, borra filas, le da igual. Si tienes un formulario o una plantilla donde el rango de suma debe quedarse quieto haga lo que haga el usuario con las filas de dentro, esta es la herramienta, y es la única. Eso sí, escribe un comentario al lado diciéndolo, porque si no la siguiente persona lo leerá como un error — y en diecinueve de cada veinte casos tendría razón.

4. R1C1 y direcciones calculadas. El segundo argumento de INDIRECTO cambia el texto al estilo F1C1, que es la forma limpia de expresar un paso relativo:

=INDIRECTO("F[-1]C"; FALSO)        la celda de arriba, sea cual sea esta celda
=INDIRECTO(DIRECCION(FILA()-1; 4)) D, una fila arriba, en estilo A1

El hilo común de los cuatro: el texto lo suministra una celda que controla el usuario o se genera con FILA()/DIRECCION() en tiempo de cálculo. En ninguno de ellos hay una coordenada de distribución escrita a mano dentro de una fórmula por un autor y dejada ahí tres años. Eso — la coordenada fija, tecleada, nunca revisada — es lo que le costó a esta contrata 111.237,15 al mes, y es una costumbre más que una función.

🎯 Escenario: La línea que hay que mantener no es «no uses nunca INDIRECTO». Es «que INDIRECTO no sea nunca el único registro de dónde vive algo». Si el nombre de hoja está en una celda y la fila se encuentra por etiqueta, la función está haciendo un trabajo real y no hay nada oculto. Si el nombre de hoja y el número de fila están los dos dentro de las comillas, has escrito una dependencia sin documentar con una coordenada tecleada al final.

12) Dos Costes Que Pagas Incluso Cuando Funciona

Volatilidad. INDIRECTO y DESREF son funciones volátiles, junto con AHORA, HOY, ALEATORIO, ALEATORIO.ENTRE, CELDA e INFO. Una función volátil se recalcula en cada recálculo del libro, haya cambiado o no algo de lo que depende, porque Excel no puede saber de qué depende. Todo lo que está aguas abajo se recalcula también.

Con veinte fórmulas esto es gratis. Deja de ser gratis rápido: un modelo de 5.000 filas con tres columnas de DESREF son 15.000 celdas volátiles más sus dependientes, recalculándose con cada edición en cualquier parte del libro, incluidas las de otras hojas. Es la causa más común del libro que se queda pensando dos segundos después de cada tecla. INDICE y BUSCARX no son volátiles y normalmente sustituyen a las dos.

El libro cerrado. Este genera más incidencias de soporte que nada de lo demás:

=INDIRECTO("'C:\Informes\[Delegaciones.xlsx]Resumen'!B18")

Eso funciona perfectamente mientras Delegaciones.xlsx está abierto, y devuelve #¡REF! en el instante en que se cierra. Un vínculo externo normal — ='C:\Informes\[Delegaciones.xlsx]Resumen'!B18 — sigue funcionando, porque Excel guarda en caché el último valor conocido de una referencia externa dentro de tu fichero. INDIRECTO no tiene valor en caché al que recurrir: tiene que resolver la referencia ahora mismo, y no puedes resolver una referencia a un libro que no está cargado. «Ayer funcionaba y hoy está todo con #¡REF!» casi siempre significa que alguien cerró el origen, y ninguna cantidad de reabrir y recalcular arreglará la fórmula: solo abrir el origen.

Unos cuantos límites más que conviene conocer, todos de la misma forma:

• INDIRECTO acepta nombres definidos y referencias estructuradas como texto — =SUMA(INDIRECTO("Tabla1[Importe]")) funciona — así que puede alcanzar una tabla por su nombre.
• Un nombre de hoja con un espacio, un guion o cualquier signo de puntuación tiene que ir entre comillas simples dentro del texto, que es por lo que la fórmula de este artículo es "'"&A2&"'!B18" y no A2&"!B18". Pon las comillas siempre; cuestan cuatro caracteres y son un seguro gratis para el día en que una delegación se llame Ellesmere Port.
• Un apóstrofo dentro del propio nombre de hoja hay que duplicarlo: una pestaña llamada Bill's Yard necesita 'Bill''s Yard'!B18.
• Pasarle a INDIRECTO un rango de nombres para evaluarlos como matriz funciona en 365 y no es fiable antes. Usa una columna auxiliar en vez de hacerte el listo.

🎯 Escenario: Si un libro se ha vuelto lento y nadie sabe por qué, cuenta las funciones volátiles antes de optimizar nada más: Ctrl+B buscando DESREF(, INDIRECTO(, HOY(), AHORA(). El arreglo rara vez es más hardware y casi siempre es INDICE.

13) Cinco Comprobaciones

Pásalas a cualquier libro que monte referencias a partir de texto. Las dos primeras cuestan un minuto y habrían cazado todo lo de este artículo la primera mañana.

1. ¿La celda que direccionas sigue diciendo lo que crees?

=INDIRECTO("'"&$A2&"'!A18")                       → "Total" en 7 filas de 20

La fórmula de mayor valor de todo esto. Caza el renombrado (#¡REF!) y la fila movida (Labour subtotal) en una sola columna, y la respuesta es una palabra en vez de un número que tengas que juzgar.

2. ¿Sigue existiendo cada nombre del que el libro se gobierna?

=ESREF(INDIRECTO("'"&$A2&"'!A1"))                 → FALSO en 5 filas
=CONTAR.SI($C$2:$C$21;FALSO)                      → 5

Esto separa «la hoja ya no está» de «el número tiene mala pinta», que es la distinción que difumina cualquier otra comprobación.

3. ¿La consolidación cuadra con algo calculado por otra vía?

La única comprobación que caza una lectura de la fila equivocada, porque esos valores son números válidos sin nada de malo. Totaliza el detalle por una ruta que no use INDIRECTO — un anexado de las veinte hojas con Power Query, una tabla plana de movimientos, o la suma de los totales propios de las delegaciones leídos por etiqueta — y compara. 693.809,05 frente a 582.571,90 no es una diferencia de redondeo.

4. ¿Cada línea se mueve como se movió el negocio?

=B2/mes_anterior                                  → 0,41 a 0,84 en ocho filas, ~1,00 en siete
=CONTAR.SI(columna_ratio;"<0,95")                 → 8

Ocho delegaciones reportando a la vez una quinta parte menos que el mes pasado no es un patrón comercial, es un cambio de distribución. Una columna de ratio contra el periodo anterior es el detector de roturas estructurales más barato que existe, y no necesita saber nada de cómo funcionan las fórmulas.

5. ¿Cuántas suposiciones sobre la distribución contiene este fichero?

Ctrl+B, Buscar dentro de ▸ Fórmulas, Dentro de ▸ Libro, y cuenta: INDIRECTO( → 20, DESREF( → 1. Son 21 afirmaciones sin documentar sobre dónde vive cada cosa, y es la lista que hay que revisar antes de renombrar una pestaña, insertar una fila u ordenar una hoja — no después.

14) Doce Trampas

1. Una referencia se mantiene; una cadena no. =B18 sigue a su celda a través de inserciones, renombrados y cortes para siempre. =INDIRECTO("B18") dice B18 hasta que alguien edite los caracteres a mano.
2. Un renombrado lo rompe a gritos y una fila insertada lo rompe en silencio, y el silencioso es el caro. Cinco #¡REF! costaron noventa minutos; ocho filas equivocadas costaron siete meses y 111.237,15 al mes.
3. Una fila equivocada devuelve un número real. De la hoja correcta, la columna correcta y el periodo correcto, al 41% u 84% de la verdad. Ninguna comprobación de rango, de signo, de tipo o de formato puede verlo.
4. La hoja de origen puede estar perfectamente bien mientras el resumen está mal. Las veinte hojas de delegación cuadraban al céntimo, porque sus propios rangos de SUMA se expandieron sobre las filas insertadas como debían.
5. Un espacio final en el nombre de una pestaña es invisible y letal. 'Tiverton'!B18 no es 'Tiverton '!B18. =LARGO(TEXTODESPUES(CELDA("filename";A1);"]")) es cómo se ve.
6. Rastrear precedentes, Ctrl+[ y la corrección del renombrado son todos ciegos ante una dirección entre comillas. La dependencia no está en el árbol de dependencias, que es también por lo que la función es volátil.
7. SI.ERROR(…;0) sobre una referencia rota es una afirmación falsa. El cero es una respuesta; el alternativo honesto es NOD(), que se propaga hasta el total y obliga a alguien a mirar.
8. Trae la etiqueta, no solo el número. Leer A18 además de B18 caza los dos modos de fallo por el precio de una columna.
9. Busca la fila, no la direcciones. INDICE/COINCIDIR o BUSCARX sobre la etiqueta sobrevive a cualquier inserción; un nombre definido de ámbito de hoja sobre la celda del total sobrevive a inserciones, borrados y cortes.
10. DESREF es el mismo trato que INDIRECTO. Excel mantiene el ancla e ignora los desplazamientos, así que una columna insertada mueve el inicio y deja el paso atrás. INDICE es el sustituto no volátil.
11. INDIRECTO no puede leer un libro cerrado. Un vínculo externo normal guarda en caché su último valor; INDIRECTO tiene que resolver ahora, así que devuelve #¡REF! en el momento en que se cierra el origen.
12. Pon comillas al nombre de hoja siempre — "'"&A2&"'!B18" — y duplica cualquier apóstrofo de dentro. Cuesta cuatro caracteres y cubre cada delegación que resulte llamarse Ellesmere Port.

Ninguna de las veinte personas de esta historia hizo nada mal. Los administrativos de delegación insertaron filas en sus propias hojas para registrar gasto que de verdad estaba ocurriendo, y sus hojas siguieron correctas. Los cinco que renombraron pestañas estaban ordenando, o codificando la red, o abriendo un segundo Grimsby, y tenían todos los motivos para esperar que Excel les siguiera, porque Excel les sigue en todo lo demás. Quien escribió el Resumen en su día resolvió un problema real de la forma obvia y produjo una columna que funcionó durante años.

Lo que lo volvió caro es que el conocimiento que el libro tenía de su propia forma vivía en veinte cadenas de texto, y una cadena no se puede mantener, no se puede rastrear, no se renombra con la cosa que nombra, y no se queja ni una vez. Cualquier otra dependencia de un libro se anuncia: una referencia real dibuja una flecha, un nombre roto pone #¿NOMBRE?, una celda borrada pone #¡REF! en color. Una coordenada tecleada no anuncia nada, porque en lo que respecta al fichero no es una dependencia. Es prosa.

Así que la disciplina es pequeña y no va de evitar una función. Que la parte variable de la referencia viva en una celda — una lista de nombres de hoja, mantenida una vez, visible para todos. Que la parte fija se busque en vez de contarse: una etiqueta, un nombre definido, una columna de tabla, cualquier cosa que sea un hecho sobre los datos y no un hecho sobre la distribución de esta mañana. Y donde una fórmula tenga que hacer una suposición sobre una distribución, pon al lado una segunda fórmula cuyo único trabajo sea decir si la suposición sigue en pie, y deja que esa falle a gritos. Haz esas tres cosas y una consolidación te avisa el día que deja de ser verdad. Sáltatelas y te avisa en marzo, con siete meses de informes ya enviados.