# Currency conversion based on own sheet

Options

Currency conversion data is saved in one sheet, based on months and years as per our corporate exchange rates.

My "main" smartsheet includes my forecast; for reporting issues, it should look-up the PO date and either utilize the latest exchange rate of currency conversion sheet (if PO is forecasted and date is in future) or utilize historical data as per the sheet.

Which formula can support here?

Tags:

• ✭✭✭✭✭✭
Options

You are probably going to end up with an INDEX/MATCH or VLOOKUP, but I would need more details to know for sure.

Can you provide a screenshot of the sheets with sensitive/confidential data removed, blocked, and/or replaced with "dummy data" as needed?

• Options

Hi Paul,

Screenshot of my simple exchange rate sheet (I only put in columns month/year as it may be required for vlookup or other formula):

Now I would like to automatically the EURO amount into USD in my forecast sheet:

Thanks again for your support, best regards

Philipp

• ✭✭✭✭✭✭
Options

Ok. To pull the data from the appropriate cell, you would want to use something like this:

=INDEX(COLLECT({Other Sheet Exchange Rate Column}, {Other Sheet Date Column}, AND((MONTH(@cell) = MONTH([PO Date]@row), YEAR(@cell) = YEAR([PO Date]@row))), 1)

But if that returns a blank we want to pull the latest which would be:

=INDEX({Other Sheet Exchange Rate Column}, MATCH(MAX(COLLECT({Other Sheet Date Column}, {Other Sheet Exchange Rate Column}, @cell <> "")), {Other Sheet Date Column}, 0))

Now we use an IF statement to say that if the first one is not blank, run it, otherwise run the second.

=IF(first_formula <> "", first_formula, second_formula)

=IF(INDEX(COLLECT({Other Sheet Exchange Rate Column}, {Other Sheet Date Column}, AND((MONTH(@cell) = MONTH([PO Date]@row), YEAR(@cell) = YEAR([PO Date]@row))), 1) <> "", INDEX(COLLECT({Other Sheet Exchange Rate Column}, {Other Sheet Date Column}, AND((MONTH(@cell) = MONTH([PO Date]@row), YEAR(@cell) = YEAR([PO Date]@row))), 1), INDEX({Other Sheet Exchange Rate Column}, MATCH(MAX(COLLECT({Other Sheet Date Column}, {Other Sheet Exchange Rate Column}, @cell <> "")), {Other Sheet Date Column}, 0)))

• Options

That sounds great. Thank you, I'll try it out!

• ✭✭✭✭✭✭
Options

Please feel free to revisit with the results.

• Options

I still got some problems with the formula suggested, also with the two separate formulas.

First formula is, once I put in the references to the other sheet by hand:

=INDEX(COLLECT({Currency Exchange Rate Column}, {Currency Exchange Date Column}, AND((MONTH(@cell) = MONTH([PO Date]@row), YEAR(@cell) = YEAR([PO Date]@row)))), 1)

Maybe I misinterpreted your suggestion... Question regarding "@cell"

do I need to type in this wording (as shown above) or do I need to reference the cell directly?

Thanks again...

• ✭✭✭✭✭✭
Options

My apologies. I'm not sure why or how, but I did manage to throw some extra parenthesis in.

=INDEX(COLLECT({Currency Exchange Rate Column}, {Currency Exchange Date Column}, AND(MONTH(@cell) = MONTH([PO Date]@row), YEAR(@cell) = YEAR([PO Date]@row))), 1)

"@cell" should be entered as is. What it does is it tells the formula to use the previously established range and evaluate on a cell by cell basis.

• Options

Error type is now changed from "#UNPARSEABLE" to "'INVALID DATA TYPE".

For information:

Currency Exchange Rate Column: formatted as "Text/Number"

Currency Exchange Date Column: formatted as "date" restricted to dates only

PO Date Column: formatted as "date" restricted to dates only

"USD" Column (where formula is put in): formatted as "Text/Number"

Sorry to bother you again...

• Options

Perfect, it worked, we are really getting close :)

Now if I take those advises to the complete formula, it runs if there is a data input in currency exchange sheet. For later dates, it delivers the failure message "#NO MATCH".

Formula I have now is:

=IF(INDEX(COLLECT({Currency Exchange Rate Column}, {Currency Exchange Date Column}, AND(IFERROR(MONTH(@cell), 0) = MONTH([PO Date]@row), IFERROR(YEAR(@cell), 0) = YEAR([PO Date]@row))), 1) <> "", INDEX(COLLECT({Currency Exchange Rate Column}, {Currency Exchange Date Column}, AND(IFERROR(MONTH(@cell), 0) = MONTH([PO Date]@row), IFERROR(YEAR(@cell), 0) = YEAR([PO Date]@row))), 1), INDEX({Currency Exchange Rate Column}, MATCH(MAX(COLLECT({Currency Exchange Date Column}, {Currency Exchange Rate Column}, @cell <> "")), {Currency Exchange Rate Column}, 0)))

Only the third part ("else" condition, shown in italic above) might need some modification, I guess.

• Options

I think I found my failure!

Last argument should read "{Currency Exchange Date Column}" instead of "{Currency Exchange Rate Column}", correct?

• ✭✭✭✭✭✭
Options

That is correct. Your last cross sheet reference should be pointing to the Date column.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!