Restricting cells to numbers only
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
Answers
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
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.
-
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?
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
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.
-
Shocked to discover restricting entry to numbers is still not a feature? That's wild.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives