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 column, the others are no longer available?  But if they remove the "x" and place it in a different column, then the same format would apply (no other columns can have an "x" when one of them does)?  

I already have Data Validation set up so that the columns have to either be blank or contain an "x" or "X".

excel
Formulas and Functions
office 365

4 Replies

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    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!

Resources