Excel 2007 : Currency Conversion

You would use

=A3/VLOOKUP("British pound",D:F,3,FALSE)

but first you have to remove the trailing spaces on the currency names in the table.

You need to adjust each to its currency name, there is nothing to tell the formula.

I was hoping there was some way of not adding in another column stating the currency, surely by formatting a cell Excel has some way of identifying how it is formatted in a formula?

Somthing along these lines.. =IF(A3=USD,VLOOKUP("US dollar",$D:$F,3,FALSE),IF(A3=EUR,VLOOKUP("Euro",$D:$F,3,FALSE),"ETC"))

Originally Posted by Richard Buttrey

Hi,

If you want the B3:B7 values to change every time the query is refreshed then you need to use a Sheet_Change event macro.
Specifically:

Please Login or Register  to view this content.

My Macro/VB is very limited, I just went to create a new macro and pasted this straight into the VB and saved.

Changed a few figures in the query (Col F) and the run again and the values in COL B didn't change as expected?

Originally Posted by Richard Buttrey

Hi,

It seems to me that the formulas are fine if you are always trying to divide 5 units of each of the specific five currencies you are using. The divisor is referring to the correct rows in the current table. The only proviso is that the web site from which you are drawing your data does not change its layout and that it is always reporting the 55 currencies you show in exactly the same order.

Do you mean the quick division formulas I put in the original example? Ignore them, they won't work on the 5,000 row report I actually want this formula in, this was merely a simple example to get a suitable formula.

Originally Posted by Richard Buttrey

Hi,

It would have helped significantly if you had indicated that you wanted 5000 rows of formulae and not the 5 cells you coloured green and specifically mentioned.

As Bob said there is nothing to tell a formula which currency row to use. I know of no easy way to interpret a format as a currency type and certainly not without a macro, but in any case the web table lists the names of currencies not their symbols so even if you extract the currency number format there is nothing to help convert it to a currency description.

Does your table of 5000 rows of information not contain a currency reference of some sort which could be used with a lookup table to establish a link to the web query table?

Ok, I have manually added in a currency reference depending on the cell formatting and then done a vlookup on this into the currency data query table.....it's a very long winded way of doing it, but if there's no other way there's no other way hey.