Excel - Create dynamic drop down lists Tutorial

In this video, you will learn how to create dynamic drop-down lists using Microsoft 365. The video covers the process of extracting a list of sellers from an order board and creating a dropdown list that includes each seller only once.
It demonstrates the use of Excel functions such as UNIQUE and SORT to organize the list alphabetically.
The video also explains how to set up the dropdown list using the built-in validation feature in Excel.
By using dynamic matrix functions, the dropdown list can accommodate new sellers added to the order sheet.
This tutorial will help you create efficient and flexible drop-down lists for your data management needs.

  • 3:26
  • 968 views
résultat(s)
00:00:00
In this tutorial we will see how
00:00:02
to create dynamic dropdown lists.
00:00:05
I want to create a dropdown list
00:00:07
that will offer me a list of sellers
00:00:09
in order to extract the number of
00:00:11
associated orders we have available
00:00:13
here in column C, a table of orders
00:00:16
which list our customers
00:00:18
associated with our sellers.
00:00:20
Then the same seller can appear several
00:00:22
times in the column since several
00:00:24
orders are associated with them.
00:00:26
To be able to create the dynamically
00:00:29
sized dropdown list that will
00:00:30
only include each seller once,
00:00:32
I will use the power of Excel
00:00:34
by the =UNIQUE function.
00:00:36
The first step will be to extract the
00:00:38
list of all sellers regardless of how
00:00:41
many times they appear on my order board.
00:00:44
To do this I have created a
00:00:46
sheet that I called list.
00:00:53
The order in which all employees
00:00:55
appear on the list is defined
00:00:57
by the order in which all
00:01:00
employees appear in column C.
00:01:01
To improve the ergonomics
00:01:03
and presentation of it
00:01:05
I will integrate another matrix function.
00:01:08
called =SORT, allowing me to
00:01:12
order each seller of this result
00:01:14
table in alphabetical order.
00:01:19
Now let's move on to creating the
00:01:21
dropdown list through the builtin
00:01:22
validation feature within Excel.
00:01:30
The problem with this validation
00:01:32
option will be to define a list
00:01:35
that will be based on a source.
00:01:37
And this source will be
00:01:38
extracted from the seller sheet.
00:01:42
The problem also will be the length of
00:01:44
the drop down list that will potentially
00:01:46
be able to accommodate new sellers
00:01:48
overtime so I could select the area.
00:01:53
But at that time my drop down list
00:01:55
would be limited to the sale and to 9
00:01:57
maximum and any seller added in the
00:01:59
order sheet that would take his place
00:02:02
would not be integrated.
00:02:05
We can however and thanks to
00:02:07
dynamic matrix functions,
00:02:08
refer to matrix results,
00:02:10
which will be the case here.
00:02:12
And it will be enough to specify
00:02:15
the starting cell by clicking on
00:02:17
it and adding the pound sign.
00:02:19
When I validate with OK,
00:02:21
we will have a dropdown list offering
00:02:23
alphabetically the list of wholesalers
00:02:25
available to be selected from this list,
00:02:28
for which I could set up as
00:02:29
it is the case here,
00:02:31
a fairly simple continuing function.
00:02:34
To a BC function allowing to count how
00:02:37
many times the same name of employees
00:02:40
presented in cell three of this
00:02:43
table will be presented in the column used.
00:02:46
If I add any additional data to my table.
00:02:50
Let's say a fake order customer
00:02:53
adds with seller Laurent Balardy.
00:03:02
We can see on the seller sheet
00:03:05
that Laurent Balardy has taken its place,
00:03:08
so it is now available in the generated
00:03:11
dropdown list here at the very top
00:03:14
with a comment associated with it.

No elements match your search in this video....
Do another search or back to content !

 

résultat(s)
00:00:00
Neste tutorial vamos ver como
00:00:02
para criar listas suspensas dinâmicas.
00:00:05
Quero criar uma lista suspensa
00:00:07
que me oferecerá uma lista de vendedores
00:00:09
a fim de extrair o número de seringas
00:00:11
Encomendas associadas que temos disponíveis
00:00:13
aqui na coluna C, uma tabela de ordens
00:00:16
que listam os nossos clientes
00:00:18
associados aos nossos vendedores.
00:00:20
Em seguida, o mesmo vendedor pode aparecer vários
00:00:22
vezes na coluna desde vários
00:00:24
as encomendas estão associadas a eles.
00:00:26
Para ser capaz de criar o dinamicamente
00:00:29
lista suspensa dimensionada que irá
00:00:30
incluir apenas uma vez cada vendedor,
00:00:32
Vou usar o poder do Excel
00:00:34
pela função =UNIQUE.
00:00:36
O primeiro passo será extrair a seringa
00:00:38
Lista de todos os vendedores, independentemente de como
00:00:41
muitas vezes eles aparecem no meu quadro de pedidos.
00:00:44
Para fazer isso, criei um
00:00:46
folha que eu chamei de lista.
00:00:53
A ordem em que todos os funcionários
00:00:55
aparecer na lista é definido
00:00:57
pela ordem em que todos os
00:01:00
os empregados aparecem na coluna C.
00:01:01
Melhorar a ergonomia
00:01:03
e apresentação do mesmo
00:01:05
Vou integrar outra função matricial.
00:01:08
chamado =SORT, permitindo-me
00:01:12
encomendar a cada vendedor deste resultado
00:01:14
tabela por ordem alfabética.
00:01:19
Agora vamos passar para a criação do
00:01:21
lista suspensa através do builtin
00:01:22
recurso de validação no Excel.
00:01:30
O problema com esta validação
00:01:32
opção será definir uma lista
00:01:35
que se baseará numa fonte.
00:01:37
E esta fonte será
00:01:38
extraído da folha do vendedor.
00:01:42
O problema também será a duração do
00:01:44
a lista suspensa que potencialmente
00:01:46
ser capaz de acomodar novos vendedores
00:01:48
horas extras para que eu pudesse selecionar a área.
00:01:53
Mas nessa altura a minha lista suspensa
00:01:55
limitar-se-ia à venda e a 9
00:01:57
máximo e qualquer vendedor adicionado no
00:01:59
folha de encomenda que tomaria o seu lugar
00:02:02
não seriam integrados.
00:02:05
Podemos, no entanto e graças a:
00:02:07
funções de matriz dinâmica,
00:02:08
consultar os resultados da matriz,
00:02:10
o que será o caso aqui.
00:02:12
E bastará especificar
00:02:15
a célula inicial clicando em
00:02:17
e adicionando o sinal de libra.
00:02:19
Quando valido com OK,
00:02:21
teremos uma lista suspensa oferecendo
00:02:23
Por ordem alfabética, a lista dos grossistas
00:02:25
disponível para ser selecionado a partir desta lista,
00:02:28
para o qual eu poderia configurar como
00:02:29
é o caso no caso em apreço,
00:02:31
uma função contínua bastante simples.
00:02:34
Para uma função BC que permite contar como
00:02:37
muitas vezes o mesmo nome dos funcionários
00:02:40
apresentada na célula três deste
00:02:43
tabela será apresentada na coluna utilizada.
00:02:46
Se eu adicionar quaisquer dados adicionais à minha tabela.
00:02:50
Digamos que um cliente de pedido falso
00:02:53
acrescenta com o vendedor Laurent Balardy.
00:03:02
Podemos ver na folha do vendedor
00:03:05
que Laurent Balardy tomou o seu lugar,
00:03:08
por isso está agora disponível no gerado
00:03:11
lista suspensa aqui no topo
00:03:14
com um comentário associado a ele.

No elements match your search in this video....
Do another search or back to content !

 

RELATED COURSES This tutorial is part of the following courses

Video Training Microsoft Excel - Advanced level

With our "advanced" tutorials, discover and master the full potential of Excel, using complex functions, macros and the power of DCTs and Power Pivot, combined with the Power Query tool.

  • Your trainer for this course
  • Cours E-learning
  • 788 Views
ALL COURSES

Reminder

Show

/

Join Live Training Session