Forum Discussion
Kim-Kay
Oct 12, 2022Brass Contributor
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".
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)