validation formulas

L

Larry

can I create a validation formula that will allow either a preset value or a
user defined value depending upon the value of another cell
 
D

Debra Dalgleish

You could use custom data validation. For example:

Select cell C6
Choose Data>Validation
From the Allow dropdown, choose Custom
In the Formula box, type:

=IF(B6="Yes",OR(C6="",C6<>""),C6<5)

Click OK

If cell B6 contains the word Yes, you can type anything in cell C6.
If cell B6 contains any other value, you can enter a number less than 5
in cell C6.
 
L

Larry

cell c9 has a drop down list "STAT, 6:45, 7:00. 7:15 etc"
if c9 = "STAT" I want c17 to equal "8"
if c9 does not equal "STAT" I want the value of c17 to be user input.
Is this possible?
Conditional validation formulas only allow the proper value without
inputting anything.
conditional formulas are erased if you have a user input in that cell.
 
J

JulieD

Hi Larry

a cell can not allow for both the keeping of a formula and user entry .. the
only way to do this is to use worksheet_change code, here's two examples to
give you an idea:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$C$9" And Target.Value <> "" Then
If UCase(Target.Value) = "STAT" Then
Range("C17").Value = 8
Else
Range("C17").Value = InputBox("Enter a value for C17")
End If
End If
End Sub

OR

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$C$9" And Target.Value <> "" Then
If UCase(Target.Value) = "STAT" Then
Range("C17").Value = 8
Else
Range("C17").Value = Null
End If
End If
End Sub
 
Top