20

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.  

 

 

Functionality
Industry

Comments

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

Andree_Stara

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

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 '/'

 

 

Andree_Stara

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

In reply to by Lainie Franklin

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.

I second Andree's suggestion.

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

In reply to by ricki

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

In reply to by Lainie Franklin

very interesting .... I created a form and was able to see the behavior described in the thread. What I found was that if I entered something like '2016,2017 or =2016,2017 from the field on the form then smartsheet knew it was purely text and didn't reformat it. It means that there is the ' ( or =) lingering in the smartsheet but that may be easier then adding extra code to concatenate multiple fields .....

2016,,2017 seems to work as well

One important factor that has been overlooked...

 

What is the purpose of collecting the data? Are you intending to compile with historical counts for tracking? Are you displaying this information somewhere?

 

Knowing your end goal for this data will most certainly help us find the solution that best works for you as right now, you do have multiple options with different outcomes presented.

In reply to by Paul Newcome

The purpose of the data is to find out what years a potential nominee for office as attended our Annual Meeting. For some people it is one year for others it is multiple -- could be ten or more. It is not a required field, so we will have to manually fill in the information if the person doing the nominating doesn't know. 

We use the data to determine whether someone is eligible to serve in specific volunteer roles. For now, the way it comes in is workable, I was just hoping to make it format the way I wanted without any manipulation. Because we don't anticipate more than 20 - 25 entries to come in, I'm ok with the way it works now. 

Thanks!

In reply to by Lainie Franklin

Ah. Ok. Well if you did go the route of using checkboxes for each year, you could then simply use a COUNTIFS function to tally up how many boxes are checked (years attended). You could also set up an update request so that you get an email only when an entry is made with NO years selected.

 

But with only 20 - 25 entries coming in, it may be easier to just continue with what is working for you.

 

Best of luck to you! smiley