Listas de validación con origen de datos almacenados en otra hoja

En el capítulo anterior de la serie validación con listas desplegables se mostró como realizar una validación utilizando listas básicas donde los datos que la componen se encuentran en la misma hoja, ahora se mostrará como hacer para crear las listas cuando los datos se encuentran en otra hoja del mismo libro.

Al momento de definir el origen de datos que componen las listas de validación, Microsoft Excel verifica que la referencia a estos datos estén en la misma hoja de cálculo que la celda a validar, por lo que en principio no se puede almacenar los datos de las listas en otra hoja (por ejemplo Lista).

Al verificar la referencia, Microsoft Excel revisa que en la misma no incluya el nombre de hoja de cálculo o libro, si lo detecta lo impide y presenta el siguiente error:

Para evitarlo, la idea es lograr escribir una referencia a un rango que apunte a otra hoja sin poner el nombre de la hoja.

Aquí se mostrarán 3 formas de como validar datos mediante una lista desplegable donde su origen se encuentre en otra hoja de cálculo del mismo libro.

Se trabajará con un ejemplo de una lista de personas donde en la primer columna se tendrá el nombre, en la segunda el país de residencia y por último la ciudad donde vive (hoja Datos).

La información sobre los países y las ciudades se validará mediante una lista de manera que el dato a ingresar sea mostrado mediante listas predefinidas.

La lista de los países válidos se encuentra en la hoja Lista en el rango I3:I6 de la hoja, y la lista de las ciudades en el rango K3:K33.

El fichero con el ejemplo completo está disponible aquí.

La primer forma de "engañarlo" es crear un nombre de rango y hacer referencia a éste en la casilla de validación.

Para esto se debe de seleccionar el rango con los datos a desplegar, y luego la opción "Nombre" del menún "Insertar" y allí seleccionar "Definir ...".

Allí se debe definir un nombre, en este ejemplo LISTA_PAISES, y donde contiene "Se refiere a" debe de estar la referencia del rango seleccionado (deberá aparecer con referencias absolutas, e incluir el nombre de la hoja).

Presionar Agregar para confirmar el nombre.

Paso siguiente se selecciona las celdas donde se ingresarán los países (de la misma forma que en el caso anterior), pero en la opción "Origen" se debe de ingresar el nombre del rango definido como

=LISTA_PAISES

Como segunda forma se puede utilizar la función INDIRECTO. Esta función convierte un texto que contiene una referencia a una celda o rango en una referencia como tal.
Aquí se debe de escribir la la dirección del rango completo en modo de texto.

En Origen se debe de ingresar:

=INDIRECTO("Lista!$A$2:$A$5")

Lo malo de esta forma es que si hay cambios en la referencia tales como cambio del nombre de la hoja, se inserta una columna antes de la A, o se insertan filas la dirección no será recalculada automáticamente y quedará apuntando a una lista errónea.

Y la tercer forma es la combinación de las anteriores: utilizando la función INDIRECTO a la que se le pasa como parámetro el nombre del rango en forma de texto.

=INDIRECTO("LISTA_PAISES")

Esta versión no presenta el problema antes mencionado ya que se trata de un nombre de rango que es modificado automáticamente por Microsoft Excel cuando se realiza un cambio.

No cabe deudas que la primer opción es la mejor y más sencilla, las otras variantes se muestran debido a que en los siguientes capítulos se van a utilizar.

Hacer el mismo procedimiento para la lista de ciudades, esta vez definiendo al rango C2:C32 como LISTA_CUIDADES.

Para la primer opción en Origen se tiene que definir:

=LISTA_CUIDADES

Para la segunda opción en Origen se tiene que definir:

=INDIRECTO("Lista!$C$2:$C$32")

Y para la tercer opción en Origen se tiene que definir:

=INDIRECTO("LISTA_CUIDADES")