validation formulas
 

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

Hi!

Need more detail. Some examples would be nice.

Biff

-----Original Message-----
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
.

You could use custom data validation. For example:

Select cell C6
Choose DataValidation
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.

Larry wrote:
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


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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.


"Larry" wrote:

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

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


----
to use this code, right mouse click on the sheet tab of the sheet containing
the list in C9 and choose view code
copy & paste this code directly into the right hand side of the screen
use ALT & F11 to switch back to your workbook and test
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Larry" wrote in message
...
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.


"Larry" wrote:

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