+ Reply to Thread
Results 1 to 4 of 4

How to make it work "='C:\Users\DK-01\Desktop\["& A1 &".xlsx]Sheet1'!$A$1"

  1. #1
    Forum Contributor
    Join Date
    09-18-2011
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    371

    How to make it work "='C:\Users\DK-01\Desktop\["& A1 &".xlsx]Sheet1'!$A$1"

    The formula below can get the value of A1 of File A and File B:
    ='C:\Users\DK-01\Desktop\[File A.xlsx]Sheet1'!$A$1
    ='C:\Users\DK-01\Desktop\[File B.xlsx]Sheet1'!$A$1

    Now the value of Cell A1 and A2 is File A and File B respectively.

    How to revise the dynamic link at Cell B1 and B2 to get the value of cell A1 at File A and B:

    "='C:\Users\DK-01\Desktop\["&A1&".xlsx]Sheet1'!$A$1"
    "='C:\Users\DK-01\Desktop\["&B1&".xlsx]Sheet1'!$A$1"

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    6,765

    Re: How to make it work "='C:\Users\DK-01\Desktop\["& A1 &".xlsx]Sheet1'!$A$1"

    Have you tried:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Best Regards,

    Kaper

  3. #3
    Forum Contributor
    Join Date
    09-18-2011
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    371

    Re: How to make it work "='C:\Users\DK-01\Desktop\["& A1 &".xlsx]Sheet1'!$A$1"

    Dear Kaper,

    Cell B1 can get the value of File A1 if File A1 is open.
    However when File A2 is open and File A1 is closed, only cell B2 can get the value and B1 become #REF!


    Please note that I have tried to get link say at cell D1,

    the formula at D1 is below when File A1 is open:
    ='[File A1.xlsx]Sheet1'!$A$1

    the formula at D1 is below when File A1 is closed:
    ='C:\Users\DK-01\Desktop\[File A1.xlsx]Sheet1'!$A$1

  4. #4
    Forum Contributor
    Join Date
    09-18-2011
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    371

    Re: How to make it work "='C:\Users\DK-01\Desktop\["& A1 &".xlsx]Sheet1'!$A$1"

    Dear Kaper,

    If my excel file, that use to extract value of A1 at File A1 and A2, is at different directory, say

    C:\Users\DK-01\Desktop\Scan

    then the value will not become #Ref.

    However, the file File A1 and File A2 should be open once; and the extracted value will become #Ref when my excel file save, exit and open again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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