+ Reply to Thread
Results 1 to 6 of 6

vlookup to Network Drive with variable filename

  1. #1
    Registered User
    Join Date
    03-14-2013
    Location
    Melbourne
    MS-Off Ver
    Office 265
    Posts
    15

    Cool vlookup to Network Drive with variable filename

    Hi all,

    I'm hoping that I explain this one properly.

    Basically I'm trying to do a vlookup on a file that sits on a network drive, that is NOT open on my computer. Basically a 'live' lookup.
    The filename changes daily based on what day it is, in a YYYYMMDD format, but I can't get the formula right, or there's a larger issue.

    I'm having issues with a value returning though. I've ended up with a #VALUE tag rather than a #REF tag.

    WITHOUT VARIABLE FILENAME (working fine)
    Returns that information I requested
    =VLOOKUP($AH4,'\\xxx.xxx\xxx\xxx\xxx\[Filename_20190625.xlsx]Sheet1'!$P$8:$T$5826,2,0)


    WITH VARIABLE FILENAME (not working)
    Returns #VALUE
    where AK1 =TEXT(TODAY(),"YYYYMMDD")
    =VLOOKUP($AH4,"\\xxx.xxx\xxx\xxx\xxx\[Filename_"&$AK$1&".xlsx]Sheet1!$P$8:$T$5826",2,0)


    NOTES
    • I've read that INDIRECT cannot work to a closed file? I have tried wrapping it in INDIRECT tags but can't get the formula correct to even test it.
    • The excel file has 10,000 odd rows and since I've implemented the formula above it's slowed down A LOT! The server is quite local.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: vlookup to Network Drive with variable filename

    You could do this using INDIRECT, but you would then also need to have the source file open.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    03-14-2013
    Location
    Melbourne
    MS-Off Ver
    Office 265
    Posts
    15

    Re: vlookup to Network Drive with variable filename

    I did read that on the internet before posting the above. I wasn't able to complete the INDIRECT function to test though.
    The end goal is to save having to open the source file daily and have the main file updated when it's opened each morning.

    Thank you.
    Last edited by AliGW; 06-26-2019 at 01:17 AM. Reason: Please don't quote unnecessarily!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: vlookup to Network Drive with variable filename

    As I said, for INDIRECT to work, source file MUST be open as well, otherwise formula will show an error.

  5. #5
    Registered User
    Join Date
    03-14-2013
    Location
    Melbourne
    MS-Off Ver
    Office 265
    Posts
    15

    Re: vlookup to Network Drive with variable filename

    Thank you. I got that part. Mentioned in my opening post.

    Looking for a solution outside of INDIRECT.

    Cheers.
    Last edited by AliGW; 06-26-2019 at 01:18 AM. Reason: Please don't quote unnecessarily!

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: vlookup to Network Drive with variable filename

    Other than using VBA, there isnt 1

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Find the drive letter of a mapped network drive
    By billyjo182 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-07-2019, 10:41 PM
  2. Variable filename in vlookup
    By Lizzietish11 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-19-2016, 08:30 AM
  3. [SOLVED] Using a Variable Filename in a VLOOKUP
    By Cpt_Matt in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-26-2014, 07:24 AM
  4. Vlookup in a database with variable filename?
    By Alexhoc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-09-2013, 09:27 AM
  5. Replies: 3
    Last Post: 07-09-2012, 03:09 PM
  6. [SOLVED] Open Network drive without mappping the drive
    By AravJ in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-05-2012, 03:14 AM
  7. Link workbooks-C drive to network drive
    By Earl in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-19-2005, 01:07 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1