Forum Discussion

sibi1235's avatar
sibi1235
Copper Contributor
Jul 25, 2024

Data validation on a column based on another cell content on a different column and different row

I am trying to creat a data validation on a column where it doesnt allow duplicate entries based of content from another column correspondence to the original entry in the first column. 

Eg:

column A - 1,2,3,4

Corresponded column B - occupied, Not occupied, Not occupied, Not occupied


If someone enters number 1 in column A again, it should trigger error message because the corresponding value in column B says “occupied “

 

hope this is clarified and hope someone can help 🙂

 

  • sibi1235 

    Let's say you enter data in A2:A50 (A1 being a header).

    Select A2:A50; A2 should be the active cell in the selection.

    On the Data tab of the ribbon, click Data Validation.

    Select Custom from the Allow drop-down.

    Enter the following formula in the Formula box:

     

    =SUM(($A$2:$A$50=A2)*($B$2:$B$50="Occupied")*(ROW($A$2:$A$50)<>ROW(A2)))=0

     

    Click OK.

    • sibi1235's avatar
      sibi1235
      Copper Contributor

      HansVogelaar WOW i spend nearly 6 hours on chatGPT to fix this and you come as an Angel and fixed this in 6 seconds ! You are amazing, Thank you so much ! I got exactly what I wanted to achieve. Thanks  again

Resources