Excel - Create dynamic drop down lists Tutorial

3:26
76 views

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.

Excel - Create dynamic drop down lists Tutorial

RELATED COURSES This tutorial is part of the following courses

Microsoft Excel Video Training – Discovery