|If you do need to have the user enter a value in a cell, I think that this Data Validation workaround will work.|
I don't believe that you can use a Nested IF in Data Validation since the first FALSE it encounters will trigger the Data Validation message, even if the overall IF statement eventually resolves to TRUE. So we need to use an simple IF function in the Data Validation wizard, one that only has a single logical_test.
This workaround will require a Helper Cell, e.g. A1
Let's say the user-entry cell that you want to validate is A9. Put this formula in A1:
This will put a 1 or 0 in A1 if the conditions you asked for are met.
Now, select Data Validation...Custom for A9 and use this formula:
When the Nested IF in A1 resolves to 1, the Data Validation will be TRUE and the value will be allowed.
Let us know if that works for you.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.