Restricting cells to numbers only

edited 12/09/19 in Using Smartsheet
10/31/18 Edited 12/09/19

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

Comments

  • Andrée Starå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

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul NewcomePaul 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.

  • 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?

  • Andrée Starå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 [email protected] and well take it from there.

    Have a fantastic weekend!

    Best,

    Andrée Starå - Workflow Consultant @ Get Done

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    edited 11/02/18

    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.

Sign In or Register to comment.