+ Reply to Thread
Results 1 to 2 of 2

Referring to closed workbook with variable file path

  1. #1
    Registered User
    Join Date
    10-22-2018
    Location
    HK
    MS-Off Ver
    2013
    Posts
    1

    Referring to closed workbook with variable file path

    - Referring to closed workbook with variable file path

    Currently I only found I could do this by INDIRECT, because INDEX could link to a closed workbook but seems not able to have variable file path referring to worksheet values.

    Example:
    3 Workbooks in total, which are Workbook A, Workbook B and Workbook C
    Workbook A: Formula driven, refer to Workbook B/C data
    Workbook B: Value only data, which is Week 1 data below
    Workbook C: Value only data, which is Week 2 data below

    Workbook A:

    - Workbook A is to show result following to drop down list (B2) selection (Week), means when I choose different weeks, suppose formula's file path will change accordingly
    1.jpg

    Workbook B:
    3.jpg
    - Source data

    Workbook C:
    4.jpg
    - Source data

    Source data will be placed accordingly in correct path, so I know the pattern of file path, but problem is only INDIRECT can only work with opened workbooks, or it will show "#REF" with closed workbooks.

    Alternatives I thought of:
    1. using INDEX instead -> but since the file path is having variables (Week 1 to Week 2), I think INDEX doesn't work
    2. using VBA -> since the path s having variables, I am not familiar with the coding...
    3. Find & Replace all formula cells (i.e. Replace all "Week 1" with "Week 2") every time -> it works but since the replace range is including "cross file", excel pop up update value alerts per line, so it is kind of annoying and inconvenient
    4. fix the path and file name, i.e. I manual rename those data and put them all in a fixed path, then everytime I open "Workbook A
    , I save as a weeklyreport -> it works too, but very inconvenient too..

    Sorry for my bad english and please see if you guys could help. Much appreciated.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Referring to closed workbook with variable file path

    #2 Use VBA is the typical solution for this. My suggestion would be to use VBA to insert a hard external reference. That is, in your example above, the VBA would detect when B2 changes and put the following into C2:

    ='C:\Week1\[Weekly Report.xlsx]Sheet1'!$C$2

    Please Login or Register  to view this content.
    Then you no longer need what's in column A.

    I don't understand what you're trying to show with workbooks B and C.

    An image attachment has very little value. Just attach the Excel file. It's easier than taking a screenshot first and then attaching that. The paper clip icon does not work for attachments. Instead, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

+ 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] Importing a text file by vba referring to a cell for file name & file path
    By Vivek2705 in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 12-01-2017, 08:54 AM
  2. Data from closed xlsm file without defined file path???
    By dbrizor in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-05-2015, 07:27 AM
  3. I need pull the data from closed workbook through assigned path loc
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-21-2014, 07:25 AM
  4. 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
  5. Data from closed workbook - issue referring to directory
    By CJ944 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-01-2011, 12:20 PM
  6. Getting <REMINDERS> from a CLOSED WORKBOOK in a fixed path
    By e4excel in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-11-2011, 04:22 PM
  7. Referring to a worksheet in a closed workbook.
    By Al Bert in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-07-2009, 08:27 AM

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