+ Reply to Thread
Results 1 to 2 of 2

How to dynamically change path name for reference to most recent closed workbook

  1. #1
    Registered User
    Join Date
    07-01-2015
    Location
    Antwerp
    MS-Off Ver
    2019
    Posts
    71

    How to dynamically change path name for reference to most recent closed workbook

    Hi,

    I have two workbooks: one is closed and one is open. And I would like to extract data from the closed one into the open one.

    I can return the value of cell A2 of the closed external workbook in my open workbook with the following formula: =INDEX('C:\path\[externalworkbook.xlsx]Sheet1'!A:A;2)

    However, every day a new workbook with a different name is added to the folder in order to represent the latest data. I want to use the most recent external workbook and I am able to identify it in my open workbook.

    My question is how can I reference the identified most recent workbook name in the INDEX path? Are there non-VBA solutions besides the INDIRECT function? (I noticed that the INDIRECT function does not allow to get data from a closed workbook.)

    Thanks

  2. #2
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: How to dynamically change path name for reference to most recent closed workbook

    imo, Excel 2013, 2016 can't use indirect with closed files.
    so, you may have only 3 possible choices.

    1. Use VBA (and you don't want to)
    2. If you know all possible filename , you may preset all formula ahead under IFERROR() function.
    =IFERROR('D:\[test.xlsx]Sheet1'!A1,0)
    (you will got open file dialog, just press cancel button to bypass them)
    3. copy new file to a static filename , and use them instead.
    for sample, 20220520.xlsx copy to today.xlsx and use today.xlsx in your formula instead of 20220520.xlsx.

    Regards.
    Last edited by menem; 05-19-2022 at 10:29 PM. Reason: add 3rd solution

+ 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. Dynamically access a closed workbook using Formula
    By wolfdemon in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-05-2021, 11:41 AM
  2. [SOLVED] Reference a cell in a closed workbook dynamically
    By mario0102 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-29-2019, 05:15 AM
  3. Reference a closed workbook, change sumifs to sumproduct
    By almostgenius in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-25-2018, 05:11 PM
  4. Replies: 2
    Last Post: 10-03-2017, 06:34 PM
  5. Find last row in closed workbook using file path name
    By amazingg64 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-27-2012, 09:22 AM
  6. Replies: 0
    Last Post: 05-30-2012, 08:27 PM
  7. Extracting a value from another (non-active) workbook using a path dynamically formed
    By WilliamCRodgers in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-16-2009, 07:37 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