theta
02-07-2012, 04:44 AM
If i could crack this would save me HOURS! every week...and everyone in my office
I have a file located here C:\RATES\SDR_RATES.xlsx
It contains historic rates for a special type of currency conversion called SDR (Special Drawing Rights).
Column A contains the period (1210, 1110, 1010, 0910, 0810) and Column B contains the currency (EUR, USD, GBP) with Column C containing the rate (1.15, 1.96, 1.145) etc...
I would like to build a function so that anybody can simply type =SDR("EUR","1210) and it will return the correct rate from column C on the closed workbook. I have several sheets that reference these rates and people have to share this workbook - which causes huge delays etc, lots of printing and changes.
Would it be possible using ADO? or using SUMPRODUCT from within a function to a closed workbook? There is a header and it is on line 1, every else is in a set format.
PERIOD....CCY....RATE
1210.......EUR....1.1500
1210.......USD....2.1700
1210.......GBP....1.6500
1110.......EUR....1.1000
1110.......USD....2.1450
1110.......GBP....1.7200
1010.......EUR....1.0090
1010.......USD....1.7801
1010.......GBP....1.5824
This would really make my year if i could get this to work (in both methods would be ideal) - and would teach me a huge amount and benefit my learning.
(would the ADO from this thread be useful starting point - http://www.vbaexpress.com/forum/showpost.php?p=259154&postcount=14)
I have a file located here C:\RATES\SDR_RATES.xlsx
It contains historic rates for a special type of currency conversion called SDR (Special Drawing Rights).
Column A contains the period (1210, 1110, 1010, 0910, 0810) and Column B contains the currency (EUR, USD, GBP) with Column C containing the rate (1.15, 1.96, 1.145) etc...
I would like to build a function so that anybody can simply type =SDR("EUR","1210) and it will return the correct rate from column C on the closed workbook. I have several sheets that reference these rates and people have to share this workbook - which causes huge delays etc, lots of printing and changes.
Would it be possible using ADO? or using SUMPRODUCT from within a function to a closed workbook? There is a header and it is on line 1, every else is in a set format.
PERIOD....CCY....RATE
1210.......EUR....1.1500
1210.......USD....2.1700
1210.......GBP....1.6500
1110.......EUR....1.1000
1110.......USD....2.1450
1110.......GBP....1.7200
1010.......EUR....1.0090
1010.......USD....1.7801
1010.......GBP....1.5824
This would really make my year if i could get this to work (in both methods would be ideal) - and would teach me a huge amount and benefit my learning.
(would the ADO from this thread be useful starting point - http://www.vbaexpress.com/forum/showpost.php?p=259154&postcount=14)