Forum Discussion
Data Validation to lock a cell based on value in another cell.
In one of my earlier replies, I attached a demo workbook in which a drop-down list appears if the cell in column A begins with "B", otherwise you cannot enter anything in the cell.
The formula was =IF(LEFT(A2)="B", $I$2:$I$6, $J$2), where I2:I6 is the source for the drop-down list and J2 is an empty cell. The 'Ignore blank' check box was cleared.
If you want to block a cell if column A begins with "B", use the same method but with formula
=IF(LEFT(A2)<>"B", $I$2:$I$6, $J$2)
If Column 1 starts with B, user cannot type in Column 3. If Column 1 does not start with B, user can type in Column 3.
- yes, what if i wanted to have two letters, for example
=LEFT(A1,2) <> "BC" Does this rule do what you want?
When I try to enter text in a row where column A does not begin with "B":
- Hmmm....could I put a max character limit on a Column 3 if Column 1 starts with B? The cell would not be locked from editing, but having len<1 and a custom error message could work. I just dont know how to do the formula.
You cannot do that with data validation, it requires that you protect the sheet and use VBA code.
It would only work in the desktop version of Excel for Windows and Mac, and all users would have to allow macros. Would that be OK?