Celdas enlazadas (Datos ► Validez...)

En este artículo vamos a aprender cómo hacer para que una celda muestre una lista de opciones permitidas de acuerdo con lo seleccionado en otra celda, por medio de Datos ► Validez… (imágenes 1 y 2). Aquí se muestra una de las posibles formas de hacerlo (casi siempre en ofimática hay varios caminos para llegar a un mismo destino). En este enlace a la revista EnRed@2.0 (IAAP) podéis leer un artículo que escribí sobre la validación de datos.

Imagen 1

Imagen 2

En este caso vamos a utilizar de ejemplo los centros directivos de todas las consejerías de la Junta de Andalucía a 01/02/2024 (fuente: www.juntadeandalucia.es). Elegida una consejería se mostrarán los centros directivos que dependen de ella y, si ya hemos seleccionado un centro directivo y cambiamos de consejería, la celda del centro directivo se pondrá con el fondo de color rojo indicando un error siempre que el centro no se encuentre entre los centros de la nueva consejería seleccionada (caso, por ejemplo, de la Secretaría General Técnica que, como se encuentra en todas las consejerías, no daría ningún error). Para esto último usaremos un formato condicional (imagen 2). En este enlace a la revista EnRed@2.0 (IAAP) podéis leer un artículo sobre el formato condicional.

 Paso 1: Crear la hoja “TABLAS”

Crearemos un hoja “TABLAS” (o con el nombre que deseéis) donde escribiremos los datos que vamos a usar de la siguiente estructura (imagen 3):

Imagen3

• En la fila 2 (columna A hasta la columna M) escribiremos los nombres de las consejerías, tal y como deseamos que se muestren (celda C2 de las imágenes 1 y 2).

• En la fila 3 y siguientes, debajo de cada consejería, escribiremos los centros directivos que dependen de ella, tal y como deseamos que se muestren (celda C3 de las imágenes 1 y 2).

• Nota: en lugar de escribir podemos usar el copiar y pegar texto sin formato, nos ahorraremos muchas pulsaciones de teclas.

Paso 1.1: Nombrar los rangos en la fila 1

En la fila 1 vamos a escribir los nombres que vamos a usar para referirnos a los rangos o intervalos de celdas que contienen los centros directivos de cada consejería. Un nombre de rango por cada consejería y una consejería por cada columna. Y, como podemos observar en la imagen 3, para evitar posibles problemas con las vocales acentuadas, los espacios y la letra “ñ”; se han sustituido respectivamente (en su caso), por vocales sin tilde, barra baja y letra “n”.

¿Cómo podemos crear esos nombres de rango de la forma más fácil? Primero seleccionaremos desde la celda A1 a la M15 y, segundo, iremos al menú Hoja ► Intervalos y expresiones con nombre ► Crear…. En la ventana “Crear nombres” dejaremos activada solamente la opción “Fila superior” y pincharemos en “Aceptar” (imagen 4). De esta manera se nos crearán 13 rangos con nombre, uno por cada columna, con el nombre, respectivamente, que figura en la fila 1 y haciendo referencia a 15 celdas cada uno.

Otra pregunta, ¿por qué tenemos que seleccionar hasta la celda M15 si la columna M sólo contiene datos hasta la fila 12, o la columna L sólo hasta la fila 7? Porque hay que seleccionar hasta el número de fila más alto que contenga datos en todo ese rango A:M. Y vemos que la columna B (Consejería de Economía, Hacienda y Fondos Europeos) llega hasta la fila 15.

Paso 1.2: Ajustar los rangos a las celdas que verdaderamente lo forman

Pincharemos en el menú Hoja ► Intervalos y expresiones con nombre ► Gestionar…, o bien, pulsaremos a la vez las teclas <Ctrl>+<F3> para ajustar cada uno de los rangos a sus celdas correspondientes (imagen 5). Por ejemplo, el rango de nombre “Presidencia”, que inicialmente tiene el valor $TABLAS.$A$1:$A$15, lo modificaremos para que quede $TABLAS.$A$3:$A$14, que son los centros directivos pertenecientes a esa consejería (observad el signo “$” para designar esa referencia como absoluta, es decir, que aunque se copie y pegue siempre seguirá referenciando a esas mismas celdas).

Imagen 4

Una vez modificados todos esos rangos deberá quedar como en la imagen 5. Cada uno de ellos haciendo referencia a los centros directivos correspondientes.

Imagen 5

 Paso 1.3: Añadir los nombres de rangos de las filas 1 y 2

Y ahora necesitamos añadir dos rangos más. Uno que haga referencia a todas las consejerías y que llamaremos L_CONSEJERIAS ($TABLAS.$A$2:$M$2) y otro que haga referencia a todos los nombres de rango de las consejerías, al que llamaremos L_NOMBRES_RANGOS ($TABLAS.$A$1:$M$1). Obviamente podéis ponerles los nombres que deseéis.

Paso 2: Crear la hoja “DATOS”

En otra hoja, a la que llamaremos “DATOS” (u otro nombre), y en la celda elegida para los nombres de las consejerías (C2) pincharemos en Datos ► Validez… . La pestaña “Criterios” así como la de “Aviso de error” deben quedar como la imagen 6. ¿Por qué si elegimos “Intervalo de celdas” luego en “Origen” escribimos “L_CONSEJERIAS”? Porque ese nombre hace referencia al intervalo de celdas o rango $TABLAS.$A$2:$M$2, es decir, desde la celda A2 hasta la M2, ambas inclusive, de la hoja TABLAS. Es indiferente usar una nomenclatura u otra pero “L_CONSEJERIAS” es más fácil de recordar. Si dejamos en blanco el mensaje de error aparecería el mensaje predeterminado de “El valor no es válido”, si se produjera un error.

Imagen 6

En la celda C3, centros directivos, actuaremos igualmente (imagen 7) ... Si marcamos la casilla “Ordenar ascendentemente” de la pestaña “Criterios” entonces las listas de consejerías y/o centros directivos aparecerán ordenadas alfabéticamente (eso queda a vuestra elección).

Imagen 7

Pero ahora en el campo “Origen” de la pestaña “Criterios” escribiremos la fórmula:

INDIRECTO(INDICE(L_NOMBRES_RANGOS;COINCIDIR(C2;L_CONSEJERIAS;0)))

Las funciones INDICE y COINCIDIR las podéis ver en el artículo del número 8 de la revista EnRed@2.0. El “0” (cero), como tercer parámetro de la función COINCIDIR, le indica que la búsqueda debe ser secuencial exacta hasta que lo encuentre o no lo encuentre.

¿Qué hace la función INDIRECTO? Devuelve la referencia (intervalo de celdas) especificada por una cadena de texto. Veámoslo con un ejemplo:

Supongamos que seleccionamos, en C2, la Consejería de Salud y Consumo, entonces COINCIDIR(C2;L_CONSEJERIAS;0) devuelve el número 5 porque esa consejería se encuentra en el quinto puesto de la lista de consejerías (celda E2 de la hoja TABLAS). ¿Y qué devuelve INDICE(L_NOMBRES_RANGOS; 5)? Devuelve el texto “Salud” porque se encuentra en quinto lugar de la lista de los nombres de rangos, celda E1. Ya sólo nos queda INDIRECTO(“Salud”) que devuelve la referencia a la que apunta ese nombre, $TABLAS.$E$3:$E$9, que son los centros directivos de la consejería mencionada. Ahora se entiende por qué en el campo “Permitir” de “Validez” elegimos “Intervalo de celdas”, ¿verdad?

Paso 2.1: Crear un formato condicional para la celda C3

Y, por último, nos queda crear un formato condicional para la celda C3 por si, una vez seleccionado un centro directivo, cambiamos de consejería, y el nuevo centro no se encuentra entre los centros de la nueva consejería seleccionada. Pincharemos en Formato ► Condicional ► Gestionar… y añadiremos la condición de “La fórmula es” (imagen 8):

SI(ESBLANCO(C3);FALSO();ESNOD(COINCIDIR(C3;INDIRECTO(INDICE(L_NOMBRES_RANGOS;COINCIDIR(C2;L_CONSEJERIAS;0)));0)))

Imagen 8

Como hemos leído el artículo del número 6 de la revista EnRed@2.0, “LibreOffice Calc: Formato condicional (segunda parte)” ya sabemos que “la condición se cumple si el resultado de la fórmula especificada es VERDADERO”. ¿Cuál será el resultado de la fórmula anterior? Si la celda C3 está en blanco, es decir, vacía, entonces devuelve FALSO y, por tanto, al no cumplirse la condición no se aplicará el estilo “Error” (imagen 9). La función ESNOD devuelve VERDADERO en el caso de que COINCIDIR no encuentre el centro directivo que busca en los centros de la consejería seleccionada en C2 y, por tanto, sí se aplicaría el estilo de celda “Error” del formato condicional a la celda C3. Los estilos se pueden modificar/crear en el menú Estilos ► Gestionar estilos, o bien, pulsando <F11> (imagen 9).

Imagen 9

Desde aquí os animo a que hagáis pruebas con otros datos que uséis en vuestro trabajo diario.

¡Atención! ¡Muy importante!

Hay que guardar frecuentemente los archivos de trabajo y siempre realizar copias de seguridad, independientemente de la aplicación con la que estéis trabajando.

El archivo más importante es el que tiene mayor probabilidad de perderse.

Muchas gracias por leer este artículo y saludos cordiales.

LibreOffice Community ver.7.6.7.2 (X86_64 es-ES) Windows 11

Se puede descargar el archivo de prácticas aquí.