Dynamic name range for use in data validation

  • Thread starter Thread starter raphiel2063
  • Start date Start date
R

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.
 
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
 
Back
Top