Data validation using adjacent cell and checking for letter "x"

[DavidCtx]

Hello,

I am trying to use data validation to check the adjacent cell for blank and the current cell must contain only the letter "x". No quotes.

I have tried many variations after searching and when I use this in another cell as a true false for testing purposes is comes back as true when D15 is blank and I type X into C15. However when I use it in custom data validation formula it will not accept it.

The cell I am configuring for DV is C15.
Formula AND(C15="x",D15="")

Thank you.

 

[tracycamp]

Maybe I don't understand what you are trying to do but it doesn't seem like this is data validation. DV is to restrict the entries of a cell to a certain set of values or type of data. Would conditional formatting be what you are looking for here. Can you provide some scenarios of what you would like to happen depending on what the user enters?


https://support.microsoft.com/en-us/help/211485/description-and-examples-of-data-validation-in-excel

 

[DavidCtx]

Thank you for your reply. I am trying to avoid using a drop down list and ensure a user only enters an X in the cell or they may leave the cell blank.

I hope this helps.

 

[DavidCtx]

Hello,

I am trying to use data validation to check the adjacent cell for blank and the current cell must contain only the letter "x". No quotes.

I have tried many variations after searching and when I use this in another cell as a true false for testing purposes is comes back as true when D15 is blank and I type X into C15. However when I use it in custom data validation formula it will not accept it.

The cell I am configuring for DV is C15.
Formula AND(C15="x",D15="")

Thank you.

I have 2 columns in my worksheet. A user may enter an X in column C or column D but not both.
Then I am checking to see if the adjacent column is blank and that the user in inputting an X only if it is.

Thanks

 

[tracycamp]

Ahhh, ok, I think I see. I don't think you can do this in a single step. If the only values that can be entered in C and D are either nothing or x, then you need to add data validation to each column where x is the only option. They user won't have to use the dropdown although it will be there. If you don't want the dropdown at all, you can skip the data validation but its more likely they'll make an error so you'll need better instructions. Next, you'll need another column that checks to see that they've entered the x in only one column with something like this =IF(OR(C2&D2="",C2&D2="x"),0,1). This function looks at the concatenation of C and D and returns a 0 if it is null or x; otherwise, it returns a 1. Since 1 represents all the situations that you don't want, you'll have to decide how to flag the user to this. Perhaps some conditional formatting on C and D. If you really want to force the user to enter an x in only one columns, and not just notify them, then that will take some VBA. Hope this helps.

 

[DavidCtx]

Thank you Tracy. This will work

Hope you have an awesome week.