A global variable, "fPriorDate" , is declared as a string, and is determined by another global variable fDate, which is "gathered" from a user via an InputBox. For instance if the user enters 31-dec-2009, then fDate = "31-dec-2009" and fPriorDate = "30-nov-2009". A folder called fDate & "_157" is then created. Within the "fDate & "_157" folder are two workbooks, one called "CDS.xlsm" and and another called fDatePrior & "_CDS".

Now here is the kicker, a third workbook, in the same folder, called Formulas needs to reference (using vlookup, and performing some calculations) both the "CDS.xlsm" and fDatePrior & "_CDS" workbooks.

Please see the excel formula below. But instead of having a hard-coded-dates, as seen below I need the formul to utilize fDate and fPriorDate.

=IF(VLOOKUP(D2,'L:\31-dec-2009_157\Support_Summaries\[CDS.xlsm]CDS'!$H$3:$J$175,3,FALSE)="divide",O2*VLOOKUP(D2,'L:\30-nov-2009_157\Support_Summaries\[30-nov-2009_157_CDS.xlsm]CDS'!$H$3:$J$175,2,FALSE),O2/VLOOKUP(D2,'L:\31-dec-2009_157\Support_Summaries\[30-nov-2009_157_CDS.xlsm]CDS'!$H$3:$J$175,2,FALSE))
.