Calculated filenames in function arguments (Office XP)

Topic

New Reply

I enter a function in a cell such as:

=VLOOKUP($A12,’E:MFAFolder[090502.xls]mfa-090502′!$A$12:$E$29,4,FALSE)

This works fine, but I want to dynamically modify the date strings (the “090502” strings) based on a calculation to be able to access a date-determined page. Logically, I want something like:

=VLOOKUP($A12,”‘E:MFAFolder[” & TEXT($G$4,”mmddyy”) & “.xls]mfa-” & TEXT($G$4,”mmddyy”) & “‘!$A$12:$E$29”,4,FALSE)

where the calculated date is in $g$4, but during the course of evaluation the 2nd argument is left as a double-quoted string, and VLOOKUP fails with a #VALUE error. Can anyone offer a suggestion?

Reply | Quote

Replies

Is the file you are reading OPEN?
I have found that if “calculated” links are used, it only works with the file OPEN. Non-calculated ones will work with the file closed. i have not found a work around. I just have a macro to open the other file minimized when the “master file” opens and close it when the master closes.

Reply | Quote

Yes, the target file is open; that requirement is kind-sorta documented in the description of INDIRECT. The problem appears to be that the second argument to VLOOKUP remains double-quoted when the whole function is evaluated; i.e., when you follow the evaluation sequence the last thing evaluated (that produces the error) is =VLOOKUP(a1,”a2″,a3,a4). The same error is given if you double-quote the second argument in the hand-entered function that works properly without the quotes.

Reply | Quote

I went for the easy answer without examining it too closely. Use INDIRECT function around the “calc name”

=VLOOKUP($A12,indirect(“‘E:MFAFolder[” & TEXT($G$4,”mmddyy”) & “.xls]mfa-” & TEXT($G$4,”mmddyy”) & “‘!$A$12:$E$29”),4,FALSE)

Steve

Reply | Quote

StarLounger, indeed! Works as desired. I suspect there’s a lot of experience behind that “went for the easy answer” line, but after experimenting with INDIRECT and trying to understand the related Help file, it does in fact make sense.

Thanks much.

Reply | Quote