Listas de validación con origen de datos almacenados en otro libro

En el capítulo anterior de la serie validación con listas desplegables se mostró como realizar una validación utilizando listas con origen de datos en otra hoja del mismo libro que los datos a validar, ahora se mostrará una variante para cuando la lista a validar se encuentra en otro 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.

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 un segundo libro que tiene una hoja llamada "Lista" donde en el rango I3:I6 de la hoja, y la lista de las ciudades en el rango K3:K33.

El fichero con el ejemplo con la lista de validación se encuentra aquí, y el que tiene la lista de los datos aquí. Se deberán de bajar ambos ficheros y dejarlos en una misma carpeta para que funcione correctamente.

Para comenzar se deben tener abiertos ambos libros, y estar en el mismo grupo de libros abiertos.

Desde el archivo donde se encuentran las listas a validar se deberán de crear los nombres de rangos de celdas que apunten al otro libro, esta vez en la referencia se deberá de anteponer el nombre del archivo donde se encuentran los datos a validar.

Al igual que en el capítulo anterior acceder a definir los nombres de las referencias (desde Insertar / Nombre / Definir), para facilitar la operación, presionar el botón que se encuentra a la derecha del "Se refiere a" donde aparecerá una pequeña ventanita, pasarse al siguiente libro con Alt+Tab, seleccionar el rango de países, y volver a presionar el botón de la derecha.

Paso siguiente se deben de definir las listas de validación para cada columna, primero seleccionar la columna correspondiente a los países, elegir la validación tipo lista (en Datos / Validación).

Si se asigna en "Origen" el nombre del rango correspondiente a la lista de países Excel no lo permitirá, emitiendo el siguiente error: "No se pueden usar referencias a otros libros u hojas de cálculo para criterios de Validación de datos".

Por lo que se deberá de utilizar la función INDIRECTO para que pase la validación "engañándo" a Excel.

Esta solución es muy similar a la tercer variante del ejemplo del capítulo anterior. La segunda también es válida, donde a la función indirecto se le pasa la dirección como tal, pero se considera mejor la opción con nombre de referencia ya que si realizan modificaciones sobre el rango de origen ésta se actualiza sola.

Como punto en contra de esta solución es que al utilizar la función INDIRECTO el libro con el origen de los datos debe de estar abierto, de lo contrario al actualizar la información retorna un error.

Realizar los mismos pasos para las ciudades, tanto el rango de datos como la validación.