Data validation

[Throughstream]

Hi I want to create a drop down list in column A sheet 1 from a list in column A in sheet 2 however I want the drop down to update if I add another Nane to the list on sheet 2 automatically.

Cheers

Dave

 

[svendiamond]

Create a dynamic named range for your list. On the ribbon, go to Formulas >> Name Manager, and click "New..." and name it whatever you want (e.g. "myList") and use this formula as the "Refers to:"

=OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,1)

Then for your data validation cell, under Validation Criteria choose "List" and as the Source refer to your list like:

=myList

 

[Throughstream]

Ah that's great!!!