Dynamic name range for use in data validation
 

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.

Dynamic name range for use in data validation
 

Try this...

Create the dynamic range...

Goto the menu InsertNameDefine
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 DataValidation
Allow: List
Source: =ListSource
OK

--
Biff
Microsoft Excel MVP


"raphiel2063" wrote in message
...
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.