Select from a List

[Seanie]

I have a dropdown list of Days set in a named range. Is it possible to
set a condition that you can only select a day that is <= to Today? -
all via Data Validation

 

[vezerid]

Assuming A2 is the validated cell, use Custom validation with the
formula

=(COUNTIF(daterange,A2)<>0)*(A2<=TODAY())

daterange is the range of excel cells that contain admissible dates.

HTH
Kostis Vezerides

 

[Seanie]

Thanks, but is it possible to do with a Drop Down list in A2?

 

[vezerid]

Not if you want a solution involving only DV. You can have List DV
(which gives you the dropdown) and additional valdiation through VBA
to test for date <=TODAY().

'------------------------------
Private remembered

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
If Int(Target.Value) >= Int(Now) Then
MsgBox "Date must be earlier than today"
Target.Value = remembered
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$2" Then
remembered = Target.Value
End If

End Sub

'--------------------------

Right-click on the sheet tab. Choose View COde. Paste the above code
in the VBA IDE window that will show up.

HTH
Kostis

 

[Max]

How about having the DV via a dynamic source range,
display only the valid dates?

Assume source dates are listed in Sheet1's A1:A20
In B1: =IF(A1="","",IF(A1<=TODAY(),ROW(),""))
In C1: =INDEX(A:A,SMALL(B:B,ROW()))
Copy B1:C1 down to C20

Then create a defined range: MyR
to refer to:
=OFFSET(Sheet1!$C$1,,,SUMPRODUCT(--ISNUMBER(Sheet1!$C$1:$C$20)))

Now you can use MyR as the source in the DVs,
and it'll always display only the list of valid dates
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik