Currency conversion based on own sheet

Philipp Welbers

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?

Paul Newcome

Ok. We're getting somewhere. This can be because of blank rows or non-date values in the date column. Let's try using some IFERROR statements.

=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)

Paul Newcome

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?

Philipp Welbers

Hi Paul,

thanks for your feedback.

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

Paul Newcome

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)))

Philipp Welbers

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

Paul Newcome

Please feel free to revisit with the results.

Philipp Welbers

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...

Paul Newcome

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.

Philipp Welbers

Thanks for your explanation!

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...

Paul Newcome

Ok. We're getting somewhere. This can be because of blank rows or non-date values in the date column. Let's try using some IFERROR statements.

=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)

Philipp Welbers

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.

Philipp Welbers

I think I found my failure!

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

Paul Newcome

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