How to I "not format" a number in a form?

Lainie FranklinLainie Franklin ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions
12/31/18 Edited 12/09/19

One of the questions on my form is what year(s) someone attended our annual meeting. The format is text/number. if someone attended in 2016, 2017, and 2018, the answer is being formatting with commas and displays as 201,620,172,018 even though I typed it as 2016, 2017, 2018. The only way around it is to direct people to put slashes between the years (2016/2017/2018). 

Any clues on how I can get Smartsheet to NOT format a number or to recognize the year as text?  I don't see a way to apply a formula when the information is coming into the sheet via a form.  

 

 

Popular Tags:
Previous1

Comments

  • rickiricki ✭✭✭✭✭

    Did you put spaces after the comma?

     

    2016, 2017, 2018

    vs

    2016,2017,2018

     

    When I pasted it from what you typed with the spaces the number looked as expected and the commas were not moved

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi Lainie,

    Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too, [email protected])

    Happy New Year!

    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.

  • edited 01/02/19

    I was going to suggest would it be worth putting the information options in a dropdown list format but just ran a test and it changes the format to the same 201,620,172,018 as you said.



    One workaround is to use 2016 / 2017/ 2018 and all the variables in a drop down  list. Format stays true to type then and you don't have to edit peoples entries to stay consistent with the '/'

     

     

    comments to slack.png

  • Andrée StaråAndrée Starå ✭✭✭✭✭
    edited 01/02/19

    Another way could be to have checkboxes for each year and then collect the information in one cell if needed.

    Would that work?

    Best,

    Andrée

    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 ✭✭✭✭✭

    I second Andree's suggestion.

  • Is there a way to select multiple check boxes in Smartsheet forms?

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    You would have multiple checkbox columns.

  • Lainie FranklinLainie Franklin ✭✭✭✭✭

    Thanks for the suggestion!

    How can I collect the information in one cell if they come from multiple checkboxes?

     

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Unfortunately, no there isn't. It would be multiple columns for the years, and then they could be joined together in one cell.

    Best,

    Andrée

    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.

  • Andrée StaråAndrée Starå ✭✭✭✭✭
    edited 01/02/19

    Happy to help!

    There are several ways to do it but the easiest in your case I think is the JOIN function.

    https://help.smartsheet.com/function/join

    Would that work?

    Best,

    Andrée

    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.

  • Lainie FranklinLainie Franklin ✭✭✭✭✭

    Thanks so much! That might be my best option. I appreciate all of you weighing in. I LOVE Smartsheet!

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    I'm always happy to help!

    Best,

    Andrée

    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 01/02/19

    I would suggest a JOIN(COLLECT( 

     

    On your sheet, you would label each column with the appropriate year. You would then use the first row to re-enter the corresponding year for each column. Your first form would populate row two. It would look something like this...

     

    Name              Years Attended              2016                2017               2018

    Blank Cell        Blank Cell                        2016                2017               2018

    John                2016, 2018                          Y                                             Y

    Mike                2017                                                            Y

    Steve              2017, 2018                                                   Y                     Y

     

    In the Years Attended column, you would use something along the lines of 

     

    =JOIN(COLLECT($[2016]$1:$[2018]$1, [2016]@row:[2018]@row, @cell = 1), ", ")

     

    This will join all of the first row together based on what was checked (shown here as "Y") and use ", " (comma space) as your delimiter.

  • rickiricki ✭✭✭✭✭

    I am not sure why it is different for me but I can type "2016, 2017, 2018" or "2016,2017,2018" in a text/number field and it displays it exactly as I typed.

    I don't understand why it is reformatting for you when it does not for me.

    Are you typing it in the box with the commas or are you just typing 201620172018. Even if I type it like that it does not reformat the way this trail is describing

  • rickiricki ✭✭✭✭✭

    if I click on the "," (thousands) in the formatting toolbar then it does reformat for me, but if I unclick that then it keeps things as I typed them

Sign In or Register to comment.