Excel data validation based on two cell values

0
votes

I want excel to restrict data entry in Column C if following conditions are True:-

  1. if Col A = "OPS" --- Restrict data entry in Col C
  2. If Col B = "Awaited" --- Restrict data entry in Col C

I tried this formula in Custom Validation:

  • =AND(A2<>"OPS", B2<>"Awaited") but its not working.

I want excel to allow data entry in Column C only if Column A contains "NPS" and Column B contains some number(or text other than "Awaited") Please Help. I have uploaded an image file...Spanshot_image

Thanks for your help in advance...

2
excelexcel-formula
You are saying Column A but in formula G2 what up??? - Harun24HR
Your condition 1 is further complicated in the text below it ("I want excel to allow data entry in Column C only if Column A contains "NPS""). This correction will fix the validation: =AND(G2="NPS", I2<>"Awaited") - Tony
@harun24hr, now corrected! - Ronald

2 Answers

1
votes

In Data validation remove tick from "Ignore blank", then if column B is blank, the formla won't allow to edit column C. I hope this helped. Write this into the data validation in the first cell 

=AND(A1="*NPS*",B1<>"Awaited")

, and just extend down.

0
votes

Use the following formula in column C.

=IF(AND(A1="NPS",B1<>"Awaited"),TRUE,FALSE)