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