Restricting cells to numbers only

Michael Culley

I noticed there's a way to restrict columns for most column types except Text/Numbers. I want to only allow numbers to be typed in and not letters because when letters are typed into those cells, it messes up all my formulas. More specifically, I'd like the editors to only be able to type in a range of numbers, for example only allowing numbers between 0-400,000. What is the best way to approach this?

 

Thanks,

Mike

Andrée Starå

Hi Michael,

Unfortunately, it's not possible natively as far as I know but it could work with a third party solution like JotForm, Google Forms, Zapier or similar.

Would that work for you?

I hope this helps you!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

Another option (doesn't prevent but does alert) would be to set up a helper column next to the text/number column and use something along the lines of

 

=IF(OR(NOT(ISNUMBER([Number Column Name]@row)), [Number Column Name]@row < 0,[Number Column Name]@row > 400000), "Please enter a number between 0 and 400,000")

 

For any row where they enter a letter or a number not within the range specified, the helper column will display

 

Please enter a number between 0 and 400,000

 

You can then set up conditional formatting to highlight the offending cell and the note.

 

This will not stop them from being able to enter letters or numbers outside of the specified range, but it does bring attention to the error.

Michael Culley

I'm not familiar with those 3rd party programs except maybe Google forms but I also don't know how to integrate that into my smartsheet.

Michael Culley

I think that's very helpful, however, my smartsheet is already pretty busy and I the people who use it mostly use the mobile app so it seems that wouldn't really be the best option. I like the idea of doing that formula in a hidden column, then using the conditional formatting to highlight the error row red but if that goes ignored then it messes up all my formulas with error messages. It would be all too convenient if smartsheet allowed columns to be restricted to numbers only. Seems silly to not have that option available especially knowing that any formula involving numbers would get error messages which then affects all associated reports and dashboards as well. Since it seems that you are very good with formulas, is it possible to setup a formula silimar to the one you provided but if the conditions are not met it automatically deletes that cell?

Andrée Starå

Can you describe your process in more detail and I'll try to give an even better recommendation?

I'd be happy to work with you and help with setting up a solution if you'd like.

Let's start with a free discovery call. How does that sound?

Please e-mail me at andree@getdone.se and well take it from there.

Have a fantastic weekend!

Best,

Andrée Starå - Workflow Consultant @ Get Done

Paul Newcome

Unfortunately not. If you have a formula in a cell, it is immediately overwritten by any data manually entered.

 

You could however build an IFERROR statement into your formulas that will essentially look at the data input and generate a blank if there is an error. Combine that with the suggestion above utilizing conditional formatting, and you will be able to still have your formulas working instead of showing an error and the problems will be highlighted.

 

You could then add in an automatic update request that goes out to the submitter that basically says, "You entered invalid data, please update with the correct stuff".

 

Would sending out a form for people to fill out be an option? If so, you could limit what fields people can enter data into (saves your formulas from being over written, and you can add in "help text" for each field of the form where you can specify what type of data should be entered in each field.

 

This would give you the ability to automate a lot, helps people see what they are supposed to enter, and requires less maintenance overall.

Ian S.

I can't believe this is still not implemented over 3 years later. The solutions presented in this thread are helpful, but at the end of the day, it's hard to imagine a reputable solution like Smartsheet not having this function built in from DAY ONE. The third party option isn't a viable solution, especially if the intent is for you to obtain the numeric only data through an Update Request through someone that doesn't have a login to the system. The best practice of a system like this is to include functions and controls that would help minimize errors in data entry. BIG miss on Smartsheet's part in the initial design. BIGGER miss not to fix it along the way. You have all these functions and formulas to calculate data and you don't have a field that is limited to numeric only entries??? I also notice you strategically left out the "smack my head" emoji. Ironic.

Emma Wetten

Shocked to discover restricting entry to numbers is still not a feature? That's wild.