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
-
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)
Answers
-
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
-
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!
-
Please feel free to revisit with the results.
-
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...
-
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...
-
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)
-
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?
-
That is correct. Your last cross sheet reference should be pointing to the Date column.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!