Forum Discussion
Data Validation to lock a cell based on value in another cell.
Use a formula such as =LEFT(E2, 2)<>"AB" and clear the check box "Ignore blank" in the Data Validation dialog (see previous reply)
Thank you! I am trying to follow your formula. If Column 1 value starts with "B", then Column 3 should allow a change. Can you see something I am doing wrong?
C2 stops me from changing the value because A2 value does not start with "B" (Pass)
C3 allows a change because A3 value starts with "B" (Pass)
C4 allows a change but A4 does not start with "B" (Fail)
If you select the entire column C when creating the data validation rule, change the formula to
=LEFT(A1)="B"
since C1 is the active cell, and since you want to be able to enter a value if column A begins with "B".
HansVogelaar Thank you so much! That worked perfectly.
Do you know if it is possible to have two behaviors in the same column depending in on the value in another field?
Same scenario as above--if Column 1 has "B" value, Column 3 can't be edited. If Column 1 does NOT have "B", then Column 3 field is a dropdown.
If there is a way to do that on one data validation formula, I would be amazed.
See the attached workbook.
Please note that the data validation range starts in C2, so the formula refers to A2 here.