Dynamic name range for use in data validation

[raphiel2063]

I'm trying to include a drop down data validation in a series of cells on 1
sheet, which generates the list based on a 'library' list in a 'library' tab.
The idea is that they change the library if they want to add a new item
rather than introducing new items all over th place.

Is there a simple way to have the list generated from a dynamic range? I.e.
the list is initally from A1:A5, but if the user goes into the library tab
and adds an item in A6, or inserts a row and enters data, the data validation
list picks this up?

(For note I've already got macros on this excel sheet based on changeevents.)

Thanks.

 

[T. Valko]

Try this...

Create the dynamic range...

Goto the menu Insert>Name>Define
Name: ListSource
Refers to:

=Library!$A$1:INDEX(Library!$A:$A,COUNTA(Library!$A:$A))

OK

Setup the drop down list...

Select the cell(s) where you want the list
Goto the menu Data>Validation
Allow: List
Source: =ListSource
OK