I'm trying to write formulas to automatically retrieve information from files whose names will change over time, and depending on the user's selections. As a current example, I want to give the user the option (via combobox) to select a report for either the 4th quarter of 2013, or the 1st quarter of 2014. I've written formulas for both the path and file names with the file dates concatenated with references to formulas in other cells that contain the relevant (and changeable) years, dates, and or quarters. Two examples of those:
1. The file for 4Q13
Path: ="C:\\Updates to EPS\"&TEXT(UT_last_quarter_start_date,"yyyy")&" Updates\Q"&UT_last_quarter_numeral&" ~ "&TEXT(UT_last_quarter_start_date,"mmmm d, yyyy")
Filename: ="GSD_Current Q"&UT_last_quarter_numeral&" "&TEXT(UT_last_quarter_start_date, "yyyy")&".xlsx"
1. The file for 1Q14
Path: ="C:\\Updates to EPS\"&TEXT(UT_new_quarter_start_date,"yyyy")&" Updates\Q"&UT_new_quarter_numeral&" ~ "&TEXT(UT_new_quarter_start_date,"mmmm d, yyyy")
Filename: ="GSD_Current Q"&UT_new_quarter_numeral&" "&TEXT(UT_new_quarter_start_date, "yyyy")&".xlsx"
I can then dim the value returned by the formula as a String and use the following code to open (for example) the file:
Application.Workbooks.Open (GSDPriorFilepath & "/" & GSDPriorFilename)
- or -
Application.Workbooks.Open (GSDCurrentFilepath & "/" & GSDCurrentFilename)
And if the user selects a different quarter for his or her report, the formulas change values just like they're supposed to, and open the appropriate files.
The problem I'm having is with getting worksheets formulas (not code) to work with the references to the path/file names.
As an example: =MATCH(UT_GM_host_country,'[&UT_GSD_Current_last_filename&]Singapore'!$B:$B,0)
The formula works fine when I initially write it, correctly returning the row # of the UT_GM_host_country-referenced workbook. But if I change the reference from _last_ to _new_, it continues using the initial reference. It doesn't matter what I change in either of the workbooks: the value returned stays the same.
I'm beginning to think I'm trying to do something with formulas that only code will allow me to do, but they do - at least initially - work fine. I'd very much appreciate someone letting me know whether it's possible to do what I'm attempting, and if it is, tell me what I'm doing wrong.