Excel addin for a series of rates!?

[theta]

Hi All...if I could get this to work (not even sure on the approach) the entire office would have a much easier day at work

There is a database of rates, stored in an excel table. There are dates (in the format MMYY) and corresponding currencies (100 in total)

I would like to have a custom function where I could type

=RATELOOKUP("EUR","0911")

And the corresponding rate will appear

The excel file with the rates is stored read only in a locked folder on the network so the format and layout will not be affected

How could I macro this? - then turn the result into an addin so that everybody can take advantage of this as it is causing HUGE headache

 

[theta]

Hmmm ok guys thanks for trying

So what would be the best approach?

Use ADO and SQL to pull the table?

Add to a collection (for each currency?)

 

[Andrew Poulsom]

If you were to write a VLOOKUP or similar formula to retrieve the rate for EUR for 0911 what would it look like if the source workbook is closed?

 

[theta]

The people in the office are not very technical with basic excel skills

Most do not know VLOOKUP

So I would like to be able to build the plugin/addin/user workbook so that everybody can simply type

=RATELOOKUP("EUR","0910") and it will return the number

Is there a simpler way of doing this?

 

[Andrew Poulsom]

I understand what you are trying to do. I wasn't suggesting that your users use VLOOOKUP. What I need to know is what formula you would use to look up the rate for EUR 0910 (assuming you know how). Perhaps then we can turn it into a UDF.

 

[theta]

Sorry this is the formula I would use (should be on a previous reply)

The rate table has a name, and cell A1 contains the word "ANCHOR"

=INDEX(PROTECTED.xlsx!LookupTable[#All],MATCH("1210",PROTECTED.xlsx!LookupTable[[#All],[ANCHOR]],0),MATCH("EUR",PROTECTED.xlsx!LookupTable[#Headers],0))

 

[Andrew Poulsom]

What would it be without this structured references? And what would it be if the source workbook was closed?

 

[theta]

I am not sure how to use a formula on a closed workbook... hmmm...

Without structed references it would look at A1:E100

The row match uses A:A, the column match uses 1:1

 

[Andrew Poulsom]

Close the workbook and repost the formula.