Dynamically Update a Drop Down Menu/List - Data Validation & OFFSET() Function

http://www.TeachMsOffice.com
This tutorial will show you how to have a dynamically updated drop down list in excel; how to make your data validation drop down list or menu dynamic. This means that every time you add something to the list the drop down menu will automatically include that item in its list.

This uses the OFFSET() function the COUNT() function and Data Validation in Microsoft Excel.


To get the spreadsheet used here of for more excel video tutorials, tips and tricks, visit the website http://www.TeachMsOffice.com You can also visit the forum there if you have any questions.

Have a great day!

---

Closed Caption:

here I'll show you how to create a
dynamically updated drop-down list or
menu
I'm assuming you've already created a
drop down list of menu using data
validation
so here I've got three products and I've
got my drop-down list right here
but what happens if i want to add
another product to a lumber
well it's not going to be included here
and we want it to be so that if the list
is updated over here
the drop down menu is also going to be
updated and quick pretty simple to do so
let's begin by going to the data
validation window
simply alt be L or in Excel 2007
data data tools data validation
now the shortcut all to dl is going to
work for all versions of excel so no
problem
now notice the list they already have
here is an absolute range a 2 a 4 so
that's why it's not going to update
anything
so what we need to do is to use the
offset in the count a function here
instead
now we can type equals offset open
parentheses but as you notice the little
excel helper doesn't pop up here so if
you have a hard time remembering
remembering all the arguments to enter
the best way for you to do this is
closed the data validation window and
write your function out here first so
the function for a dynamic list is going
to be equals offset 0 FF s ET
open parenthesis now the first thing you
select is the reference
so the reference is going to be the
first item in the list
do not select the title product but the
first thing , the next is a row which
we're going to put 40 in the next column
now these two zeros 14 rows and one for
column simply means we don't want the
reference here to move if we put
something else instead of zeros here it
would tell the offset function that we
want our reference cell to be one to the
right or up or down or left
so that's what that means the most
important part is the next one so , now
we choose the height
so how high do we want it to be well
we're going to use the count a function
for that so co un ta open parenthesis
and now if you're an Excel 2007
simply select the entire column
previous versions of Excel may only
allow you to use a certain range
reference so say cell a1 to a 500 but
not the entire column now close the
account a and we're going 2-1 then close
the offset function
close parentheses now the minus one is
simply here because the count a function
is going to count all non empty cells so
this right here is going to also count
the product sell and that means it's
going to give us one extra cell that we
don't want in our list basically because
what it's going to do is going to go to
the reference cell and count down five
because it includes the product sell but
we don't want to count down that one
extra one
so that's what the mines one means
now hit enter
and don't worry about what it spits out
here simply highlight all this
copy that control C escape go up to our
menu here
no alt dl and down here in the source
window
delete all of this and go ahead and
taste our offset function in there
and now you can see it highlights all of
the cells in the list
let's go ahead and click OK
we add something else here
we shall see the list is updated so
screws and metal are now updated here if
we delete all the first two
we can see that the list updates only
saw and hammer but go back
now it's dynamically update and anytime
you add anything to the next row
it's going to update in the list here I
don't worry about this sample thing that
we created
go ahead and delete them the other
things to know our go back to the data
validation window
explain what the - one here meant
now if you have more than one title
above your list you're going to have to
subtract as many cells as you have text
or numbers and which are above your list
so if above product I had the name
so John Doe then product than my list
I would have to put a minus to hear
naught minus 1 so i would have to make
this - 1-2 if I had three cells with
text or numbers above the beginning of
my list
it would have to be a minus 3
so that's how that works
and also if you wanted to you could put
this as a named reference
so you wouldn't have to actually put the
offset function in your data validation
you can use named reference but
that's another tutorial so that's how
you can make I dynamically updating list
or menu that drops down