Data Validation - Formula for Number and Text dropdown.

[Newbienew]

Hello Again,

I am looking for a formula to put in a data validation dropdown, if there is one, that will allow the selection of 1-50 & N/a. Some one at work mentioned 1,2,3,4,5,6,7,8,9,10... ,N/A under custom. But doing that all the way up to 50 seems like a lot. Someone said columnize it, 1-50 in a column and add N/a so its like a source. But I am not trying to do that as some rows might get deleted. Is there a better way about doing this. Thank you in advance.

 

[6StringJazzer]

You can use a custom formula that will enforce the rule.

=OR(AND(H4>1,H4<50),H4="N/a")

If you want to see a dropdown list, you have to have the entire list someplace, as described by your two suggestions above. If you are worried about rows getting deleted, just put it on another sheet that won't be used for anything else. You can even hide the sheet to get it out of the way.