Results 1 to 6 of 6

Thread: Looking for a way of using a text cell value in a link formula linkiing multiple file

  1. #1

    Looking for a way of using a text cell value in a link formula linkiing multiple file



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am attempting to use a value in a cell in one file (value in A2{cell contains 'Wk 38 May 10, 2014'}) and the value in A2 is the name of a worksheet in another file, so I want to create a link formula that will use the value in A2 to identify the worksheet to reference to within the link formula.

    Example='[Book2]Wk 38 May 10, 2014'!$A$10, would like to replace the 'Wk 38 May 10, 2014' with the value that is in cell A2 of the worksheet that contains the formula.

  2. #2
    Quote Originally Posted by Sackie01 View Post
    I am attempting to use a value in a cell in one file (value in A2{cell contains 'Wk 38 May 10, 2014'}) and the value in A2 is the name of a worksheet in another file, so I want to create a link formula that will use the value in A2 to identify the worksheet to reference to within the link formula.

    Example='[Book2]Wk 38 May 10, 2014'!$A$10, would like to replace the 'Wk 38 May 10, 2014' with the value that is in cell A2 of the worksheet that contains the formula.
    Hi
    Try this formula, adjust as necessary.
    Good luck.
    Tony

  3. #3
    Quote Originally Posted by OTPM View Post
    Hi
    Try this formula, adjust as necessary.
    Good luck.
    Tony
    Hi Tony, think I need the formula though....

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,464
    Articles
    0
    Excel Version
    Excel 2016
    Try:

    =INDIRECT("'"&A2&"'!A10")

    Note: That you cannot use this to reference a closed workbook, though. The workbook must be open.


  5. #5
    Quote Originally Posted by NBVC View Post
    Try:

    =INDIRECT("'"&A2&"'!A10")

    Note: That you cannot use this to reference a closed workbook, though. The workbook must be open.
    Actually I am looking for a formula that would work with a close file.

    I have been testing the CELL function but I cannot seem to get this to work inside of a formula.
    ='C:\My Documents\[Test file.xlsx]CELL("contents",A2)'!$A$10 does NOT work but I cannot figure out my error.

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,464
    Articles
    0
    Excel Version
    Excel 2016
    You can't "indirectly" reference a closed workbook with Excel's base functions.

    You will need to use a UDF.

    Here is a popular one called: PULL from Harlan Grove


Posting Permissions

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