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

New 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:




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!!




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