How can I convert a Local currency to USD in the Smartsheet Form.

Nateetorn Rakkhaphan

If I need to convert the local currency to USD in the Form, Can I do that? For example, if I fill the THB2000 in some field in the Smartsheet form. Then, I need the form to automatically convert the THB to USD. Is it possible to do that.

LizTo

Hi @Nateetorn Rakkhaphan,

I use a lot of forms where the person filling out the form enters the amount and then chooses which currency. You could do something similar.

On the sheet, I have a helper column that pulls in the exchange rate previously entered into a separate metrics sheet, and then it will autocalculate it into the main currency that I need (USD).

I get the exchange rates from oanda.com and enter them manually into the metrics sheet, and then Smartsheet does the rest.

This is what it looks like on the metrics sheet:

And this is what it looks like on the sheet where the form entries show up:

The column "Exchange Rate" column uses an INDEX MATCH formula to pull the exchange rate from the metrics sheet.

The "USD Equivalent" column is a formula to convert it into USD.

Paul McGuinness

Hi @Nateetorn Rakkhaphan

Unfortunately, forms cant perform active calculations, like converting currency or totalling a basket for checkout etc.

Sorry

Thanks

Paul

Andrée Starå

Hi @Nateetorn Rakkhaphan

I hope you're well and safe!

Unfortunately, it's not possible now, but it's an excellent idea!

Please submit this as a Product Feedback or Idea (If it hasn't been added already) when you have a moment.

Here's a possible workaround or workarounds

• Add the form side-by-side with a Report or Sheet, have them make the conversion there, and then input it in the form.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

LizTo

Hi @Nateetorn Rakkhaphan,

I use a lot of forms where the person filling out the form enters the amount and then chooses which currency. You could do something similar.

On the sheet, I have a helper column that pulls in the exchange rate previously entered into a separate metrics sheet, and then it will autocalculate it into the main currency that I need (USD).

I get the exchange rates from oanda.com and enter them manually into the metrics sheet, and then Smartsheet does the rest.

This is what it looks like on the metrics sheet:

And this is what it looks like on the sheet where the form entries show up:

The column "Exchange Rate" column uses an INDEX MATCH formula to pull the exchange rate from the metrics sheet.

The "USD Equivalent" column is a formula to convert it into USD.

Cari Burleson

Hi @LizTo

Your solution is exactly what I'm needing for a sheet that I manage. I am not familiar with the "matrix sheet" so could you explain this to me? Also, is it possible for you to share the index match formula that you mentioned in your post? TIA!!

LizTo

Hi @Cari Burleson,

A metrics sheet is a separate sheet that you can use behind the scenes to use to run calculations that you can then pull into others sheets where needed. In this case I use the metrics sheet to put the exchange rates.

The index match formula I am using looks like this: =INDEX({USD Equ}, MATCH(Currency@row, {Currency}, 0))

Replace with the column headers or references that you need for your sheets.

You may find this video helpful for learning more about INDEX MATCH: https://www.youtube.com/watch?v=CJJwfvIYhM0