Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Apr 2002
    Location
    Polk City, Florida, USA
    Posts
    31
    Thanks
    3
    Thanked 4 Times in 2 Posts

    Calculated filenames in function arguments (Office XP)

    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?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Calculated filenames in function arguments (Office XP)

    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.

  3. #3
    Lounger
    Join Date
    Apr 2002
    Location
    Polk City, Florida, USA
    Posts
    31
    Thanks
    3
    Thanked 4 Times in 2 Posts

    Re: Calculated filenames in function arguments (Office XP)

    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.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Calculated filenames in function arguments (Office XP)

    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

  5. #5
    Lounger
    Join Date
    Apr 2002
    Location
    Polk City, Florida, USA
    Posts
    31
    Thanks
    3
    Thanked 4 Times in 2 Posts

    Re: Calculated filenames in function arguments (Office XP)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •