Dynamic Data Validation

[tm23]

I have numeric values (0, 1, 2, 3, or 4) in cells for each of columns I, J,
K, L.

User can also enter values (0, 1, 2, 3, 4, or 5) in the cells under columns
W, X, Y.

Now for each row, if any of the cells under the columns I, J, K, and L
contain a value 1, user may only be allowed to enter any of the values 1, 2,
3, 4, or 5 in the cells under the columns W, X, and Y in that row, ELSE user
may only be allowed to enter 0.

My first question is how may I force this validation?

My second question is how do I ensure integrity across both sets of columns?

Really appreciate any help or pointer! Thank you

 

[ryguy7272]

This is a great resource for things pertaining to data validation:
http://www.contextures.com/xlDataVal01.html

HTH,
Ryan---

 

[joel]

Use the worksheet menu Data - Validation - custom


the formula in the Validation should return either a TRUE (can enter
datat into cell) or FALSE (cannot enter data into cell)


So you formula can look like this

Put this formula in cell W1 data validation. I assumed that the cells
can't equal 1.5
=if(I1=1,Or(W1=1,W1=2,W1=3,W1=4,W1=5),W1=0)