Empecemos por lo básico: ¿qué es un rango dinámico? El rango A1:A5 en la fórmula =SUMA(A1:A5) es estático.
Si agregamos una fila dentro del rango, Excel lo ajustará automáticamente convirtiendo la fórmula a =SUMA(A1:A6).
Pero si insertamos la nueva fila entre la fila 5 y 6 de nuestro ejemplo, Excel no ajustará el rango de la fórmula
Para superar este problema podemos convertir el rango A1:A5 de la fórmula en dinámico usando rangos nominados ("nombres"). Seleccionamos la última celda del rango (A5 en nuestro ejemplo) y usamos como referencia en un nombre que llamaremos "ultima_celda" (sin acentos y sin espacios en blanco que nos aceptables en nombres) la celda inmediata superior (A4 en nuestro ejemplo)
Hay que prestar atención a que la referencia es relativa (la dirección de la celda no incluye las "anclas" $)
Ahora podemos escribir nuestra fórmula de esta manera:
Otro tipo de situación se presenta cuando queremos que el rango de una lista se extienda o contraiga de acuerdo a la cantidad de elementos (celdas ocupadas) que la componen. Un ejemplo es cuando creamos una lista de valores para una lista desplegable con validación de datos.
La receta clásica es usar la función DESREF como en este ejemplo
DESREF crea un rango empezando en el "ancla" (la celda A1 en nuestro caso) y usando la función CONTARA para determinar cuántas filas están incluidas en él.
Para crear listas desplegables dependientes usamos la función INDIRECTO.
Aquí comienza la cuestión con las funciones volátiles. ¿Qué es una función volátil?
No todas las fórmulas de una hoja son recalculadas cada vez que introducimos un cambio. Excel determina qué celdas serán afectadas por el cambio y cuáles no y sólo recalcula las primeras.
Sin embargo hay algunas funciones que son recalculadas con cualquier cambio en la hoja. Estas son las funciones volátiles. Por eso se suele recomendar como buena práctica evitar, en la medida de lo posible el uso de estas funciones.
Entre las funciones volátiles de Excel se cuentan, justamente, DESREF e INDIRECTO.
Podemos construir rangos dinámicos sin usar DESREF e INDIRECTO con estas técnicas:
1 – si usamos la versión 2003 de Excel o posteriores, la mejor alternativa en mi opinión es usar Listas (Excel 2003, Tablas en Excel 2007/2010). No nos complicamos la vida con fórmulas complejas que a veces pueden producir errores inadvertidos y le dejamos que todo el trabajo lo haga Excel por detrás de las bambalinas.
2 – Usar una combinación de INDICE y CONTARA:
=Hoja1!$A$1:INDICE(Hoja1!$A:$A;CONTARA(Hoja1!$A:$A))
Finalicemos la nota diciendo que la influencia de las funciones volátiles en una hoja de Excel será significativa sólo cuando se haga uso intensivo de ellas, algo similar a lo que ocurre con las fórmulas matriciales. Por eso, y como en muchos otros aspectos de la vida, las cuestiones no son absolutas. Usar funciones volátiles no es ni buena ni mala práctica; todo es una cuestión de cantidad.
