Forum Discussion

Kim-Kay's avatar
Kim-Kay
Brass Contributor
Oct 12, 2022
Solved

Formula/Format for Multiple Category/Column Options

I have a spreadsheet with multiple questions that people can answer by putting an "x" in one of five columns.  Is there a way to limit their answers, though, so that if they place an "x" in one colum...
excel
Formulas and Functions
office 365
  • Lorenzo's avatar
    Lorenzo
    Oct 12, 2022

    Hi Kim-Kay 

     

     

    With the above example, select C3:G5 > Data (tab) > Data Validation > Allow = Custom, Formula =

    =AND(C3="x",COUNTIF($C3:$G3,"x")=1)
Kim-Kay's avatar
Kim-Kay
Brass Contributor
Oct 12, 2022
Can we add in the requirement that the field can only be left blank, or have a lower case x, or an upper case x?
Kim-Kay's avatar
Kim-Kay
Brass Contributor
Oct 12, 2022
Nevermind. I see now that the x/X/blank is included in the formula for the data validation.

Thank you very much!
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Oct 12, 2022
    You're welcome. Glad this helped & Thanks for providing feedback
    Regards

Resources