Data validation to accept a list and a number

[JoeSw]

Hi there,

I was wondering if anyone knew whether it was possible to create a data validation rule that allows you to select from a list (text options) OR allows you to enter a number.

Thanks!

Joe

 

[Jeffrey Mahoney]

You could turn off the Error Alert for the validation list and it would allow you to enter a number, but it would also allow you to enter anything else.

We could create a VBA trigger that would check the cell contents. It could check your list and see if the item exists there, then it could check to see if the value is a number.

 

[JoeSw]

You could turn off the Error Alert for the validation list and it would allow you to enter a number, but it would also allow you to enter anything else.

We could create a VBA trigger that would check the cell contents. It could check your list and see if the item exists there, then it could check to see if the value is a number.

Thanks for answering Jeffrey.

Yes, I'm trying to reduce the amount of erroneous entries into a column so there's hopefully fewer mistakes made so getting rid of the error alert might be a bit of a risk. But a possible suggestion.

I had thought about writing something in VBA but the only problem is the spreadsheet is quite large, on sharepoint and accessed by multiple people at the same time so I'd be a bit worried about using that.

Joe

 

[Jeffrey Mahoney]

Other alternatives. If you make a custom formula in a Data Validation, you can test for multiple criteria, but you won't have a list to choose from.
=OR(COUNTIF(List1,J18)>0,ISNUMBER(J18))

Here's a fun one: Add a formula to the bottom of your list and make sure the Data Validation includes that new cell. Make the formula like this.
=IF(ISNUMBER(J18),J18,"#NA")
Where J18 is the cell with the Data Validation. Now you can choose from a list and enter any number