Data Validation to lock a cell based on value in another cell.

Copper Contributor

Hello Excel Community,

 

I have a relatively simple spreadsheet with several validation rules and conditional formatting applied.

 

My data is in a table with headers in Row 1.  Table Column E has validation applied as a drop down list with "Y" or "N" as choices.  I'm trying to block input in table column F unless the corresponding cell in column E2 equals "Y".  I've done so much searching and as far as I can tell the following formula should work:

 

=AND(E2="Y",E2<>"")

 

This works as expected if "N" is selected in Column E, but if E is left blank, column F is still open for any value.

 

What am I doing wrong??

 

Microsoft® Excel® for Microsoft 365 MSO (Version 2303 Build 16.0.16227.20202) 32-bit

 

Many thanks!!

 

 

 

9 Replies
You only need E2="Y"
That will either be True or False.

Hi @Roger Govier,

 

Thanks for your reply.  I'm assuming you mean =E2="Y".

 

With that formula in the custom validation, it still allows entry if E2 is left blank.  That's why I added the addtional statement E2<>"", but that had no effect.  For context, E2 also has a validation list of Y or N.  If neither is selected, any value is still allowed in the cell with validation rule =E2="Y".  I don't think the validation list in E2 has any effect because without validation list in E2, the same thing happens: Validation in F doesn't work if E is left blank.

@Jrhodes303 I have the same issue; adding a comment to follow the responses. I have a cell that I want to be locked from editing IF another cell has a value that starts with a certain prefix. 

@Jrhodes303 

Clear the check box "Ignore blank" in the Data Validation dialog.

HansVogelaar_0-1718292837003.png

HansVogelaar_1-1718292919351.png

@asmith40 

Use a formula such as =LEFT(E2, 2)<>"AB" and clear the check box "Ignore blank" in the Data Validation dialog (see previous reply)

@Hans Vogelaar 

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)

asmith40_0-1718295632774.png

 

@asmith40 

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".

 

@Hans Vogelaar 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. 

@asmith40 

See the attached workbook.

HansVogelaar_0-1718307295316.png

Please note that the data validation range starts in C2, so the formula refers to A2 here.