Data validation - allow a specific character OR range of numbers?

[mikeblogg]

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?

[#2]

@mikeblogg 

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.

[#3]

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!

[#4 · Accepted solution]

@mikeblogg 

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)))

[#5]

That's perfect! Thank you so much!