I assume you are talking about a data validation list not a drop-down menu.
If that's not the case please clarify.
What's going on is that you've indicated that a range (say Sheet1!A1:A10)
contains your list. When you add more data to cell A11, A12, etc, the list
looks like expands but the data validation rule doesn't know that. I get
around this a couple of different ways.
1) Easy way - Start with a larger list. When you define the first list for
data validation, include extra cells. When I take this approach I enter
something in these cells that gets typed over in the future to remind me
that they are part of the list. These "reserved" values will show up in the
drop down list and can be selected so keep that in mind. For instance, the
list might look like...
Apples
Bananas
Grapefruit
Reserved1
Reserved2
Reserved3
2) Sort of Easy way - Insert new values in the middle of the list instead of
at the end. If you current list is in cells A1:A10, select row 10 and
insert new rows. Add the new entries in the newly created rows. Since the
value originally in cell A10 has now been pushed down to A12, the data
validation list automatically expands. You can also drag the value in cell
A10 down to make room. Once you've added the new values, you can resort the
list.
3) Tricky method - You've probably noticed you can only reference a list in
another sheet by using a named referenced versus pointing to that sheet or
typing in the sheet name and cell references. You could use the offset
formula to create a dynamic range name reference. So that the range
automatically expands as you add more data at the bottom of the list. The
formula depends on the setup of your sheet. Assume you have a sheet called
"dvLists" for all of your data validation lists. Also assume Row one
contains a description of the list, the list itself starts on row 2, and
each column has only one list. If the Fruit list was in column A it'd look
something like...
Row 1 Fruit
Row 2 Apples
Row 3 Bananas
Row 4 Grapefruit
Now go to Insert > Names > Define. In the Names in Workbook field type
something like FruitList. In the Refers To field type the formula,
= OFFSET(dvLists!$A$2,0,0,COUNTA(dvLists!$A:$A)-1)
then click Add and OK
Now anytime that you add something to the bottom of the list, the range
reference dynamically expands. Caveat, you must not have any blank cells
between cell A2 and the bottom of the list in column A.
Hope that helps
John
www.JohnMichl.com