Automatically prefill Exchange Rate Column from other sheet

Wesley Verschaeren

Hi,

I have a base sheet where I do calculations in different currencies (USD, EUR, CHF & GBP).

I have created a metrics sheet where I keep quarterly exchange rates between the different currencies (attached you can see an example).

In the base sheet I have added an Exchange rate column which I would like to populate based on the exchange rates in my metrics sheet. Based on this column I would do an automatic calculation from the initial currency to USD.

How it should work:

If only the exchange rate for Q1 is entered, the formula should take the correct exchange rate for the currency and prefill it in my base sheet. If the exchange rates for Q2 are entered, the formula should use the exchange rate for Q2. If the exchange rates for Q3 are entered, the formula should use the exchange rate for Q3 and so on.

Which formula(s) should I use to accomplish this scenario?

Many thanks for your help :)

KPH

Could you add an extra column to your metrics sheet to just count the rows (either an auto number column or with a column formula - see below for formula ideas)?

That way you will be able to identify the latest exchange rate entered based on MAX of that column rather than messing around with IF statements.

Then you can do an INDEX of the exchange rate, MATCHing on Currency and MAX row number.

Number each row

This formula would give you a simple running count of rows

=COUNT([Currency Exchange]$1:[Currency Exchange]@row)

Or this one will count the empty rows as well, which it doesn't look like you need, but I find it safer to ensure there are no duplicates

=COUNTIFS([Currency Exchange]$1:[Currency Exchange]@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell))))