+ Reply to Thread
Results 1 to 5 of 5

Dynamically updating file path based on date cell within a VLOOKUP

  1. #1
    Registered User
    Join Date
    10-20-2021
    Location
    Canada
    MS-Off Ver
    Microsoft Excel for Office 365 MSO - desktop version
    Posts
    16

    Dynamically updating file path based on date cell within a VLOOKUP

    Hi there,

    I have a file which, using a VLOOKUP references a closed workbook(s) depending on the date in Col A.
    For example, if the date in Col A is 11/1/2022, it will use the below formula to find the file located in that location and return the value which is working fine. The formula is in Col D:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    However, at the moment, each day I am manually changing 'Nov 01 2022' to the current day in Nov. Next month I will have to change 2022-11 to 2022-12 and also change 'Nov 30 2022' to ;Dec 01 2022' within the formula.

    I want to have the formula written in a way that it automatically updates based on the current day or current parameters listed within the inputs in Col M&N or based on the date in Col A or Col H (same date just different layouts to try and match the file name structure).

    My attempt at this which is returning an NA is in Col I:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Any help would be hugely appreciated.

    Thank you
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by GW1994; 11-23-2022 at 08:42 PM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Dynamically updating file path based on date cell within a VLOOKUP

    To do that you need INDIRECT... but it does NOT work on closed sheets.

    Options:

    1. Consolidate all sheets into ONE file. Then there are no problems.

    2. Set up a HORRENDOUS looking formula using the CHOOSE function to select the correct (closed) file.

    3. Use VBA. If that's your chosen option, I will move the thread to the VBA sub-forum.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    10-20-2021
    Location
    Canada
    MS-Off Ver
    Microsoft Excel for Office 365 MSO - desktop version
    Posts
    16

    Re: Dynamically updating file path based on date cell within a VLOOKUP

    Hi Glenn,

    Thank you for the response. I have seen it working in various other examples so I believe its definitely possible. The below is a working example for a different use, not a VLOOKUP, but a file path that updates based on Inputs:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Dynamically updating file path based on date cell within a VLOOKUP

    Exactly. It is NOT a lookup.

    You still have 3 options... and ONLY 3 options.

    or 4. Power query might do it too....

  5. #5
    Registered User
    Join Date
    10-20-2021
    Location
    Canada
    MS-Off Ver
    Microsoft Excel for Office 365 MSO - desktop version
    Posts
    16

    Re: Dynamically updating file path based on date cell within a VLOOKUP

    Ok, thank you Glenn. Lets move it to the VBA sub-forum and see if anyone can help.
    Thanks for your help.

+ 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. VBA to change file path based on cell value
    By TitansGo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-30-2016, 05:21 PM
  2. Saving Excel File/Path Based On Cell Value
    By jeremyfraley in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-25-2015, 06:00 PM
  3. Macro to open a file and folder that has date in the path based on a cell value
    By mm671750 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-07-2015, 06:31 PM
  4. Replies: 8
    Last Post: 08-12-2013, 01:12 PM
  5. VBA for Updating Cell validation dynamically based on the RadioButton Selection
    By Rem0ram in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-24-2012, 03:14 PM
  6. Asking to find file path when updating?
    By whizzards23 in forum Excel General
    Replies: 0
    Last Post: 07-07-2008, 12:40 PM
  7. Replies: 4
    Last Post: 08-05-2005, 08:05 AM

Tags for this Thread

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