Lista desplegable autocompetable

-

En una entrada anterior ya hablamos sobre listas desplegables que se actualizaban automáticamente. En esta ocasión vamos a ver como crear listas desplegables que se autocompletan a medida que empezamos a escribir. 

Esto añade un avance a la utilidad de las distas desplegables, sobre todo cuando estas listas contienen grandes cantidades de registros. Si bien es cierto, que esto se puede subsanar con la creación de listas dependientes, y tal y como ya comentamos en la entrada sobre ejemplos de aplicación de la función INIDRECTO., hay que pensar que esta solución requiere, que nuestro formulario tenga que utilizar al menos dos listas desplegables para poder seleccionar el dato buscado.

En esta ocasión, vamos a ver la solución sin necesidad de utilizar macros, con el siguiente ejemplo. Puedes descargar el archivo para practicar aquí.

Por un lado, tenemos un listado de artículos con su precio establecido, así como una lista de clientes, y deseamos utilizar listas desplegables para conformar las facturas de venta. Ambas tienen formato de tabla, de modo que cuando se introduzcan nuevos datos estos se incorporen al desplegable automáticamente

Dado la gran cantidad de clientes y artículos, queremos que nuestras listas desplegables se autocompleten o mejor dicho se filtren al escribir parte del texto, para confeccionar nuestra factura.

Desplegable para clientes. 

En primer lugar, vamos a crear una celda donde escribiremos parte del texto para que, a partir del dato ahí contenido, pueda filtrar los datos.


En la celda P2 escribimos la función: 
=HALLAR(P1;Clientes[Clientes])
Y en aquellas celdas en la que encuentra la combinación de letras Mar, indica en que posición se encuentran dichos caracteres.
A continuación vamos a conseguir que las celdas que arrojan el error #¡VALOR!, se conviertan en FALSO, y las que muestran un valor numérico  en VERDADERO.
Para ello utilizamos la función ESNUMERO.
=ESNUMERO(HALLAR(P1;Clientes[Clientes]))
Ahora con la función FILTRAR, traeremos aquellos datos que se corresponden con el valor VERDADERO. El primer argumento será la columna Clientes, el segundo (el criterio de filtro), la función que ya hemos escrito, y el tercer argumento (cunado no existe esa coincidencia, indicaremos que muestre Dato no disponible.
=FILTRAR(Clientes[Clientes];ESNUMERO(HALLAR(P1;Clientes[Clientes]));"Dato no disponible")
Si además queremos que se muestren ordenados alfabéticamente incorporamos la función ORDENAR:
=ORDENAR(FILTRAR(Clientes[Clientes];ESNUMERO(HALLAR(P1;Clientes[Clientes]));"Dato no disponible"))
Ahora acudimos a nuestra factura, y nos situamos en la celda donde queremos crear la lista desplegable. Desde la pestaña Datos,  hacemos clic en Validación de datos y cumplimentamos la ventana del siguiente modo:
Si abrimos el desplegable de nuestra lista, observamos que los datos que nos muestra son los que se han filtrado.
Si queremos que el desplegable cambie cuando empecemos a escribir debemos modificar la fórmula que hemos creado en la hoja Listados, porque el dato a hallar no es el de la celda P1, sino lo que escribamos en la celda de validación, así nuestra fórmula quedará del siguiente modo.
=ORDENAR(FILTRAR(Clientes[Clientes];ESNUMERO(HALLAR(Factura!E4;Clientes[Clientes]));"Dato no disponible"))

Si ahora en nuestra celda de validación escribimos las letra Sa, y hacemos clic en el desplegable nos mostrará el siguiente error:

Ello es porque ese texto como palabra literal no existe en la lista, para evitar este error, volvemos a abrir Validación de dato y hacemos la siguiente modificación, desmarcando la opción de Mostrar mensaje...

Ahora ya nos muestra el desplegable con aquellos nombres que contienen dichos caracteres.
El resto de datos del cliente que necesitamos para la factura podemos cumplimentarlos con la función BUSCARV o incluso con la función FILTRAR.

Desplegables para productos

Por lo que se refiere a los productos también queremos utilizar lista desplegable, pero no solo una, sino varias, lo que exige una "pequeña" variación respecto a lo visto antes. Inicialmente seguiremos los mismos pasos que antes:
  • Creamos una celda (E1) donde inicialmente, escribimos el texto que servirá de base para el criterio en la función hallar.
  • En E2, escribimos la función: =HALLAR(E1;Lista[Objetos]), y nos devuelve en aquellas celdas donde se encuentra la combinación de letras escrita, valores numéricos indicando que posición ocupan dichos caracteres.
  • Añadimos la función es número para cambiar los valores numéricos por VERDADERO y el resto por FALSO: =ESNUMERO(HALLAR(E1;Lista[Objetos])).
  • Aplicamos la función Filtrar y Ordenar: 
=ORDENAR(FILTRAR(Lista[Objetos];ESNUMERO(HALLAR(E1;Lista[Objetos]));"No hay datos"))
  • A hora que ya tenemos el rango de datos que queremos mostrar en el desplegable, acudimos a la hoja facturas y creamos las listas de validación. Para hacerlas todas de una, seleccionamos desde la celda B11 a B21, y accedemos al comando Validación de datos, donde cumplimentamos la ventana del siguiente modo:
  • En la pestaña Mensaje de error desmarcamos la opción mostrar mensaje.
Ahora es cuando llega la diferencia. En el caso anterior, cuando sólo teníamos una lista de validación para el dato cliente, cambiamos el origen en la función Hallar, para que apuntará a la celda donde se encuentra la lista de validación. Si ahora hiciéramos lo mismo tendríamos que hacer escribir tantas funciones como listas de validación y que cada función apuntase a una lista distinta. !Uff¡

La función CELDA()

Esta función devuelve información acerca del formato, ubicación o contenido de la primera celda, según el orden de lectura de la hoja, en una referencia. Consta de dos argumentos:
  • Tipo de información. Es un valor de texto que especifica el tipo de información de la celda que se desea obtener.
    • Dirección: la referencia, en forma de texto, de la primera celda del argumento ref. 
    • Columna: El número de columna de la celda del argumento ref.
    • Color: Valor 1 si la celda tiene formato de color para los valores negativos; de lo contrario, devuelve 0 (cero).
    • Contenido: Valor de la celda superior izquierda de la referencia, no una fórmula.
    • Archivo: Nombre del archivo (incluida la ruta de acceso completa) que contiene la referencia, en forma de texto. Devuelve texto vacío ("") si todavía no se ha guardado la hoja de cálculo que contiene la referencia.
    • Formato: Si la celda tiene formato de color para los números negativos, devuelve "-" al final del valor de texto. Si la celda está definida para mostrar todos los valores o los valores positivos entre paréntesis, devuelve "()" al final del valor de texto.
    • Paréntesis: Valor 1 si la celda tiene formato con paréntesis para los valores positivos o para todos los valores; de lo contrario, devuelve 0 (cero).
    • Prefijo:  Devuelve un apóstrofo (') si la celda contiene texto alineado a la izquierda, comillas (") si la celda contiene texto alineado a la derecha, un acento circunflejo (^) si el texto de la celda está centrado, una barra inversa (\) si la celda contiene texto con alineación de relleno y devolverá texto vacío ("") si la celda contiene otro valor.
    • Proteger: Valor 0 (cero) si la celda no está bloqueada; de lo contrario, devuelve 1 si la celda está bloqueada.
    • Fila: El número de fila de la celda del argumento ref.
    • Tipo: Devolverá "b" (para blanco) si la celda está vacía, "r" (para rótulo) si la celda contiene una constante de texto y "v" (para valor) si la celda contiene otro valor.
    • Ancho
  • Referencia. La celda sobre la que desea información es opcional.
De todas estas utilidades de la función celda es que si en tipo elegimos Contenido y no indicamos ninguna referencia: en una primera instancia nos genera un error de referencia circular, ya que le estamos pidiendo que nos muestre como resultado el contenido de la propia celda. Pero a partir de ese momento, el ultimo valor que escribamos en cualquier celda del libro se reflejará en la celda donde ubicamos la función celda.
De ese modo, si en E1, que es donde escribimos los caracteres a hallar, ubicamos la función:
 =CELDA("contenido"),
Cuando nosotros empecemos a escribir en cualquiera de las listas de validación que hemos creado, esos caracteres se verán reflejados en E1, y así el desplegable ya funcionará correctamente

Comentarios

Publicar un comentario

Entradas populares de este blog

Tablero KANBAN

-
Imagen
Un tablero Kanban es una herramienta para mapear y visualizar un flujo de trabajo. Las versiones más simples constan de tres columnas: para distinguir las tareas que están pendientes de hacer, las que están en marcha y las que ya están hechas. Mientras que las filas representan los diferentes tipos de actividad. Su lógica es simple, si en una columna entran las tareas más rápido de lo que salen el trabajo comenzará a acumularse y el problema se hará visible para todo el equipo, Habiendo libertad para crear tantas columnas o subsecciones que se necesiten para visualizar un flujo de trabajo, según el proyecto, empresa o actividad; en este post tan solo vamos a mostrar una forma de como construir un modelo, con el fin de que puedas aplicar el modo a tus necesidades. Vamos a desarrollar el trabajo de dos modos en función de si somos usuarios de 365 o no. En ambos casos partimos de la misma tabla  Actividades . Aquí te puedes descargar el archivo. También puedes este post en YouTube El...
Leer más

Buscar y traer múltiples imágenes en Excel

-
Imagen
  La función BUSCARV, nos permite obtener información, en base a un valor buscado, y esa información puede estar en otro libro o en otra hoja de cálculo. En este ejemplo, contamos con una plantilla, en la que, seleccionando el nombre del equipo, se mostrarán los ID, de cada uno de los miembros de dicho equipo, así como la información relativa de cada uno de ellos. Esta información la tenemos disponible en la hoja BASE, así como una imagen de cada uno de ellos. Pero, además, queremos que nos muestre una imagen de cada uno de los integrantes del equipo. Para ello en la hoja Base, tenemos el nombre de la imagen, tal y como está guardada en la carpeta Emoticones. El archivo con el que estamos trabajando está dentro de la carpeta Ejemplo Macros , que a su vez contiene la carpeta Emoticones, donde están las imágenes de cada uno de los jugadores. Observa que los nombres de los archivos de la carpeta Emoticones, coinciden con los que figuran en la columna foto de hoja BASE. Así p...
Leer más

Si(), SI.CONJUNTO() y BUSCARV()

-
Imagen
 Tres formas de resolver el mismo problema. En el ejercicio que planteamos deseamos conocer el importe a pagar por cada uno de los pasajeros del vuelo Dallas-Bogotá.  El precio del billete está marcado en 220 USD, pero se aplica una serie de descuentos en función de la edad del pasajero, datos que tenemos recogidos en la tabla Tarifas: Por otro lado tenemos el listado de pasajeros con su fecha de nacimiento y edad: Solución con la función SI() La función SI(prueba lógica; [valor_si_verdadero];[valor_si_falso]), dispone de tres argumentos: Prueba lógica . En este apartado de la función nos solicita que le planteemos " una pregunta"  cuya respuesta sea "Sí"  o "No" . Valor si verdadero.  En este segundo argumento, nos pide que indiquemos que valor, función u operación deseamos realizar en el caso de que la respuesta a la pregunta sea Sí . Valor si falso. Por último, en el tercer argumento, nos pide que indiquemos que valor, función u operación deseamos reali...
Leer más