Data validation list

[mehbodo]

Is there a way to make a data validation list select an entire column bar the first row?

ie

Ref number
0001
0002
0003
0004

DV List will read

0001
0002
0003
0004

and if i decide to update source by adding 0005 it will be added to the DV List.

 

[MickG]

With you validation cell "F1" and the validation list in column "A" try this:-

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]If[/COLOR] Target.Address(0, 0) = "F1" [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    [COLOR="Navy"]With[/COLOR] Target
    .Select
    .Validation.Modify xlValidateList, xlValidAlertStop, _
    xlBetween, "=" & Rng.Address & ""
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[mehbodo]

isn't that visual basic? how do i add that into excel?

sorry i'm not that advanced yet..

 

[MickG]

Right click your sheet Tab, Select from Menu "View Code". VB Window appears.
Paste code into VB Window.
Change code ranges to suit, See Code
Close VB Window.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng As Range
' Change Address Below (Red Font) of validation CELL  to Suit
If Target.Address(0, 0) = "F1" Then   
 Change Range (Red Font) of validation LIST  to suit 
Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    With Target
    .Select
    .Validation.Modify xlValidateList, xlValidAlertStop, _
    xlBetween, "=" & Rng.Address & ""
End With
End If
End Sub