Currency conversion based on own sheet

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?

Best Answer

Answers

  • Paul Newcome
    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?

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

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
  • 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
    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.

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

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

  • I think I found my failure!

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!