Forum Discussion
Data Validation to lock a cell based on value in another cell.
See the attached workbook.
Please note that the data validation range starts in C2, so the formula refers to A2 here.
Thanks again. Is there a way to make the drop down show when column 1 does not start with B? This formula is for if it does start with B; that helped me with my scenario. However, I have another situation I could use this formula in where it would be great to show a drop down if NOT starting with B. It could start with anything else.
- 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?
- Thanks again. This is slightly different. I don't want a drop down in the cell.
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. 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)
- Do you know if there is a validation to disable a cell based on another cell staring with B?
Change LEFT(A2)="B" in the formula to LEFT(A2)<>"B"