Drop Down list Help

Hi, i really need some help with drop down lists.

What im trying to do is have one worksheet that has a vertical list of names for ex. mel
aaron
hannah
but i want to be able to continue adding names to this list that will appear on a drop down list on another worksheet.
I know how to make a drop down list and have it work from another worksheet. What i want to know is can i make a drop down list based on the continually adding data on the first worksheet?
Thanks and i hope i this is enough information!

Hi Elle,

I assume by drop down list you mean cell validation. Although ... what I am going to describe will also work with drop-down boxes and combo boxes as well. It also works for charts and pivot tables. So, it's a pretty handy trick.

What you want is a range reference that changes based on the number of items in the list, right? The way to do this is to create a Name that is a live formula instead of a static range address.

Let's assume the list you are starting with is on Sheet1 in cells B6:B10. To begin, create a Name the way you normally would ... for example, select the range and use Insert >> Name >> Define and give it a Name; let's use aRng for the name.

Now, in the Define Name dialog at the bottom you see something like this:
=Sheet1!$B$6:$B$10

Change that Refers to formula to be something like this:
=OFFSET(Sheet1!$B$6,0,0,COUNTA(Sheet1!B:B),1)

The way I wrote the COUNTA function, it will count every entry in column B; so, if you have other stuff in that column [other than your list], you might need to make an adjustment to that part of the formula.

The OFFSET function is pointing at the first name in the list, then saying step off from there 0 rows and 0 columns (i.e., use the starting cell), and make the list "count" rows by 1 column.

Hope this helps.