Top-loading form data does not take on currency or decimal place formatting in sheet
In many of my forms, I select the option to have new rows added to the top so they are easy to find. When I do this, however, I find that most of the time, the data does not keep the numerical formatting I've applied to the column previously. This makes automated purchase order approvals come through without the $, and when it defaults to tenths instead of hundredths it causes problems for my payroll team who have to pay to the 1/4 hour. How can I ensure new data takes on the format I want? Interesting point - the color format is kept, but not numerical format.
In the example below, the Total Cost field is a calculated field from the columns on the right (which are pulled in from a separate sheet using Index/Match function). As you can see, my newest entries from the form are not showing currency format.
Similarly, when my late call hours come in from supervisors, I only see one decimal place, but the payroll must calculate to .25 or .75 of an hour so I need to capture that for their export.
Any help is appreciated as this is only 2 of many examples. ~Stacey
Best Answer
-
I may have found a work around for this issue @Eric Law! Not a perfect solution, but by wrapping my formula that calculates cost in a VALUE() function, my currency column formats seem to be holding up with new data. I had to create a new field for the decimal issue since that value is directly from form input, not calculated, but =VALUE(1*[Form Entry]) solved that decimal problem as well. Seems excessive, but it's working so far. Thanks for your comments!
Answers
-
Hello @Stacey C have you used the number format in the top right?
If you apply the money to the column it should format it as $xxxx.xx by default. In addition, you can choose the decimal amount too. Just brainstorming
If this doesn't work, how does the data get inputted? Via form? -
Thanks for writing back, @Eric Law! Yes, I have highlighted the column and selected $ and .00 for my respective columns, but the formatting will not hold on a sheet where the new form entries are submitted to the top of my list. There are very few forms that I have load to bottom of the list, but I haven't noticed this same issue on those particular forms.
If you hear of any other way to fix this issue, I would appreciate your input! Have a great day.
-
@Stacey C I see what you mean... That is annoying. So you will need to apply the $ format or the hundredths format before exporting every time. Formulas don't even seem to work if you always need the hundredth decimal point... I am stumped as well. Sorry.
-
I may have found a work around for this issue @Eric Law! Not a perfect solution, but by wrapping my formula that calculates cost in a VALUE() function, my currency column formats seem to be holding up with new data. I had to create a new field for the decimal issue since that value is directly from form input, not calculated, but =VALUE(1*[Form Entry]) solved that decimal problem as well. Seems excessive, but it's working so far. Thanks for your comments!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives