Forum Discussion
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 🙂
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.
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
