Forum Discussion

Jrhodes303's avatar
Jrhodes303
Copper Contributor
May 02, 2023

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

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

 

 

 

    • Jrhodes303's avatar
      Jrhodes303
      Copper Contributor

      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.

  • asmith40's avatar
    asmith40
    Copper Contributor

    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. 

    • 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)

      • asmith40's avatar
        asmith40
        Copper Contributor

        HansVogelaar 

        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)