Differentiating USD Vs CAD in Itemized Form/Sheet

RachelMa

I have an itemized expense reimbursement form that collects information on funds in USD, CAD, or a mix of both. Many of the submitters live in Canada and travel in the US, so it's necessary to have a mix of both currencies on the reimbursement form.

Right now, I have a question asking if the funds are USD, CAD, or both. So if it's just USD or CAD, we're covered.

Any ideas or solutions to organize the reimbursement requests that contain both USD and CAD?

Here's what I've thought of:

1. Adding radio buttons below each item to mark whether it's USD or CAD. But that will add 50+columns to this behemoth--so not ideal. Plus it's a bit finicky for the submitter.
2. Having them put "$1, USD"; "$4, CAD"; "$80 USD"; etc. But then the sum formulas in my sheet won't work because of the extra characters.
3. Have the user submit two forms. One for USD expenses and the other for CAD. But that seems strenuous on the submitter, so I probably won't get approval for it.

Any other solutions that would be easy on the submitter and the people monitoring the sheet?

Thank you!

Rachel

L_123

I'm confused as to why you would need 50 more columns for one more radio button. Can you extrapolate on this?

RachelMa

hi, L_123!

Each existing column in my sheet (there are around 50) is for specific categories of expenses on specific days. For example, there's "Day 1: Breakfast," "Day 1: Lunch," "Day 2: Taxis," "Day 2: Hotel," etc. We need to keep that rigid for accounting purposes.

Day 1 Breakfast might be in Toronto and they pay in CAD, and lunch is in Los Angeles and they pay in USD. So every itemized expense would need to be marked as USD or CAD.

To do that, I think I would need radio buttons for each item category/existing column, thus doubling the amount of columns in my sheet.

Maybe there would be a way to hide/unhide multiple columns in one swoop? That way the USD vs CAD columns could easily be displayed but hidden most of the time. Just throwing out ideas.

Thanks!

Andrée Starå

Hi @RachelMa

I hope you're well and safe!

Could you inform the users to add a code to the amount, and then we can work some magic in the sheet after submission instead?

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!

Sherry Fox

@RachelMa ,

How about 2 columns on your sheet, one for USD and the other for CAD. Then be sure BOTH are listed on your form. Your users can then enter in the correct field for this currency type. Then your sum functions will be correct, as all your USD and all your CAD will be summed separately.