Forum Discussion
Solved
Data validation - allow a specific character OR range of numbers?
Hi, I'm trying to restrict values entered into a cell to "x", "/", or a whole number in the range of 5 to 29. I have worked out how to do it using a drop-down list, but I'd prefer to not have the drop-down lists. Is there a way to do this using a custom formula?
Change the formula to
=OR(H2="x",H2="/",ISNUMBER(MATCH(H2,SEQUENCE(25,,5),0)))
or
=OR(H2="x",H2="/",ISNUMBER(MATCH(H2,ROW($5:$29),0)))
Let's say you want to apply this to H2:H50.
Select this range. H2 should be the active cell in the selection.
Use data validation of type Custom with formula
=OR(H2="x",H2="/",ISNUMBER(MATCH(H2,SEQUENCE(29),0)))
if you have Microsoft 365 or Office 2021, or
=OR(H2="x",H2="/",ISNUMBER(MATCH(H2,ROW($1:$29),0)))
if you have an older version.
- That'll do the job! It still allows the numbers from 1 through 4 to be input, but for my intents and purposes, it doesn't really matter. Thank you so much!
Change the formula to
=OR(H2="x",H2="/",ISNUMBER(MATCH(H2,SEQUENCE(25,,5),0)))
or
=OR(H2="x",H2="/",ISNUMBER(MATCH(H2,ROW($5:$29),0)))
