Currency Value with decimal in form

Stowi

Hi,

I have a sheet with several currency formatted columns. The data can be entered with a form which has an "numbers only" validation for each currency column (renounce this validation doesn`t influence the issue)

If you enter an integer without decimal, everything is fine. The value is displayed as a currency in the sheet.

But if you have a decimal, e.g. 40.50 or 40,50 , the value is not formatted as currency.

As a result a total that is made from this column the non formatted values are not being summed up.

really hope somebody has a solution for that.

Jeff Reisman

@Stowi I can recreate what you're seeing with regards to the currency format showing up. Strange.

If the form submitter uses the currency symbol in their entry, that comes through on the sheet. Values entered directly into that column show up with currency format as well.

Either way, however, I don't see any issue when summing the values, whether they are currency formatted or not. You can see my sum on the values in the red box added up correctly. As far as having the values show currency format correctly in the sheet - In a helper column (formatted for US Currency) I just brought over the value from the column the user entered their value into, and it showed up as currency. See the purple box, the value $50.40 came from 50.40 in the column to the left.

In the red box -

$60.50 was added directly into the sheet as 60.50, and the dollar sign appeared automatically.

$40.50 was added via form as 40.50. It showed as 40.50, and when I reselected currency format for the column again, the dollar sign showed up.

50.40 was added via form, and column was not re-selected as currency format, so it remains 50.40.

$30.50 was added via the form as $30.50.

Stowi

Hi Jeff,

thank you for the fast response. Yeah you are right the sum is including even the number not being formatted BUT what was actually the issue here (sorry for that) is that the value is also shown in a report. If all values are formatted as currency you have the € symbl and the thousand separator. But as soon as one value is NOT currency format, the sum also will not be displayed as currency. What looks horrible if you have huge numbers .

Help columns are not an option for me, because my sheet(s) has 20+ currency columns and is part of a bigger, complex setup.

Resetting the column format can not be the solution *lol

Adding the currency symbol can work but also fail, if you write it for example at the end. Then the number is being shown as a text value.

Why can`t the form just handle decimals as currency? It is not that unrealistic for me to have cents too ;)

Jeff Reisman

@Stowi I can recreate what you're seeing with regards to the currency format showing up. Strange.

If the form submitter uses the currency symbol in their entry, that comes through on the sheet. Values entered directly into that column show up with currency format as well.

Either way, however, I don't see any issue when summing the values, whether they are currency formatted or not. You can see my sum on the values in the red box added up correctly. As far as having the values show currency format correctly in the sheet - In a helper column (formatted for US Currency) I just brought over the value from the column the user entered their value into, and it showed up as currency. See the purple box, the value $50.40 came from 50.40 in the column to the left.

In the red box -

$60.50 was added directly into the sheet as 60.50, and the dollar sign appeared automatically.

$40.50 was added via form as 40.50. It showed as 40.50, and when I reselected currency format for the column again, the dollar sign showed up.

50.40 was added via form, and column was not re-selected as currency format, so it remains 50.40.

$30.50 was added via the form as $30.50.

Stowi

Hi Jeff,

thank you for the fast response. Yeah you are right the sum is including even the number not being formatted BUT what was actually the issue here (sorry for that) is that the value is also shown in a report. If all values are formatted as currency you have the € symbl and the thousand separator. But as soon as one value is NOT currency format, the sum also will not be displayed as currency. What looks horrible if you have huge numbers .

Help columns are not an option for me, because my sheet(s) has 20+ currency columns and is part of a bigger, complex setup.

Resetting the column format can not be the solution *lol

Adding the currency symbol can work but also fail, if you write it for example at the end. Then the number is being shown as a text value.

Why can`t the form just handle decimals as currency? It is not that unrealistic for me to have cents too ;)

Jeff Reisman

@Stowi I think you should definitely open a support ticket and based on what they say, submit a feedback/enhancement request. There's also an area here in the Community for feedback & enhancement discussions: https://community.smartsheet.com/categories/smartsheet-product-feedback-%26-ideas

Jeff Reisman

@Stowi I know it seems impractical to use helper columns. Have you considered using a separate sheet & form to collect the responses, and then using formulas to bring those values into your real sheet? Force number-only validation on the form, and then use the VALUE function wrapped around and INDEX/MATCH or INDEX/COLLECT to be extra sure? (Sometimes form validation doesn't work so well on mobile.)

Stowi

@Jeff Reisman

I really appreciate your help and using help columns or help sheets might be the solution for other cases. But in this case, it would be far to complicated.

Anyway, thanks for your help and the hints! Hopefully Smartsheet could solve this issue in the near future.

ascott24_SOSGLOBAL

We should be able to maintain the column format (currency in this example) even if the form entry is only a number (55.00 vice $55.00). I cannot force my users to fill the form field with a leading $ to maintain the format but that would be nice as well. Should not need a separate column to then connect with a formula to maintain formatting for cells and reports, etc.

Genevieve P.

Hi all,

Jumping in here to let you know that Forms received a number of updates this week - one of which is that entries with decimals will retain the currency formatting in your sheet!

Here's more information: NEW! Forms minor updates and enhancements

Cheers,

Genevieve