PDA

View Full Version : Drop down data validation list



sujittalukde
02-01-2008, 05:46 AM
I have created a drop down list via data validation. The list contains the following items:
Sunday
Monday
Tuesday
Wednesday
Thuesday
Friday
Saturday

Now I want that if in a cell of column L any one item is used the same should not appear again in the other cells.ie say the item Monday is selected in L3 then from L4 & onwards the item Monday should not come.

Solutions may be Macro or non macro based.

Oorang
02-01-2008, 07:46 AM
One way of many:

Public Sub AddUniqueValidationToSelection()
Dim rng As Excel.Range
Dim val As Excel.Validation
Set rng = Selection
Set val = rng.Validation
With val
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=COUNTIF(" & rng.Address & "," & _
Replace(rng.Cells(1, 1).Address, "$", vbNullString) & ")<=1"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Unique Values Required"
.ErrorTitle = "Invalid Input"
.InputMessage = "You must enter a unique value in this range."
.ErrorMessage = "The value you entered already exists in this range."
.ShowInput = True
.ShowError = True
End With
End Sub

sujittalukde
02-01-2008, 09:44 PM
Thanks for the reply But I also want a drop down list to choose the items. How can I include the same ie drop down list also?

Bob Phillips
02-02-2008, 01:44 AM
See http://www.contextures.com/xlDataVal03.html