-1

I have question and answer spreadsheets with up to a couple hundred rows in them. One column in each row indicates a correct answer by entering a number from 1 to 6 or multiple answers separated by commas like 1,3,5. I'd like to enforce this rule on every row and not allow input of anything else.

I find can't a way to use Custom Data Validation and am not sure how to use a function in each row.

JvdV
  • 70,606
  • 8
  • 39
  • 70
Velocedge
  • 1,222
  • 1
  • 11
  • 35

1 Answers1

1

  • Select your range of interest, for example A1:A10

  • Add the following formula as a custom data validation:

    =IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s[preceding::*=.  or .*0!=0 or .*1>6 or .*1<1]"),"")=""

You'd need Excel2013 onwards but the formula will validate the string value to have:

  • No duplicate values.
  • No values other than numbers.
  • No values above 6.
  • No values below 1.
  • Only comma will work as a valid seperator.

The A1 in the formula is relative so validation for A10 will work just the same way but then for A10 obviously.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • I'm using 2016 and the answer looks perfect... nearly anyway! ;-) Since I have hundreds of rows, is there any way to make the A1 to A10 relative? I actually will be using B3-B200+ so manual entry would not be so fun... especially with custom input/alert text. – Velocedge Sep 15 '20 at 12:48
  • @Velocedge, select your range `B3-B200` and change `A1` in the formula to `B3`. Not sure what you meant but with *relative* I meant you only need to add this validation formula **once** to the selected range. Another option would be to use an actual table I suppose. Not tested that one. – JvdV Sep 15 '20 at 12:50
  • 1
    My bad... I was selecting the entire column and that didn't work. When I selected the rows B3:B400 it worked exactly as desired. – Velocedge Sep 15 '20 at 13:07
  • This is crazy... I need to change to allow 0-9 but I can't seem to make that work! – Velocedge Nov 10 '20 at 17:49
  • If you have a different question you can open up one to make it more clear what it is you are after? @velocedge – JvdV Nov 10 '20 at 18:56