Results 1 to 8 of 8

Thread: Reference Cell in External Spreadsheet Reference String

  1. #1
    Acolyte Joecam's Avatar
    Join Date
    May 2014
    Posts
    27
    Articles
    0
    Excel Version
    xlsm

    Reference Cell in External Spreadsheet Reference String



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

    I'm trying to write a formula that will allow me to reference external spreadsheets and which contains a reference to a cell within the current spreadsheet.

    This is how the current reference looks:
    ='C:\Users\camb\Desktop\Project Tracking\[PT 040519.xlsx]Overview'!$G$2

    I want to replace the "PT 040519" with a reference to cell A2. I've tried a concatenate formula but I keep getting an error.

    Does anyone have any suggestions on how to accomplish this?

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,662
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Perhaps try the INDIRECT function like =INDIRECT("First_part_of_reference"&A2&"2nd_part_of_your_reference"
    Thank you Ken for this secure forum.

  3. #3
    Acolyte Joecam's Avatar
    Join Date
    May 2014
    Posts
    27
    Articles
    0
    Excel Version
    xlsm
    That didn't work. I am still getting an error.

  4. #4
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013
    Perhaps
    From Opened Wbk without path
    Code:
    =INDIRECT("'["&A$2&".xlsx"&"]"&"Overview'!$G$2")
    From Opened Wbk with path
    Code:
    =INDIRECT("'C:\Users\camb\Desktop\Project Tracking\"&"["&A$2&".xlsx"&"]"&"Overview'!$G$2")
    From Opened or Closed Wbk (This formula use INDIRECT.EXT function from 'MOREFUNC Add-in for Excel')
    Code:
    =INDIRECT.EXT("'C:\Users\camb\Desktop\Project Tracking\"&"["&A$2&".xlsx"&"]"&"Overview'!$G$2")
    Please see attached file

    Did it help?
    Attached Files Attached Files
    Last edited by navic; 2019-04-16 at 05:31 PM. Reason: Atachment added
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  5. #5
    Acolyte Joecam's Avatar
    Join Date
    May 2014
    Posts
    27
    Articles
    0
    Excel Version
    xlsm
    Navic, your formulas do work. Unfortunately I do need this to work with closed workbooks and it's impractical for me to use the MOREFUNC add-in. Is there anyway to overcome that?

  6. #6
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013
    Excel INDIRECT does not work with a closed workbook. The INDIRECT.EXT function is part of the MOREFUNC add-in package and it works with a closed workbook. I do not see the reason why you should not install MOREFUNC Add-in if you need to work with a closed workbook?
    Is there anyway to overcome that?
    Perhaps VBA?
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  7. #7
    Acolyte Joecam's Avatar
    Join Date
    May 2014
    Posts
    27
    Articles
    0
    Excel Version
    xlsm
    The problem with downloading an add in is that I would need to have numerous people download it in order to share the spreadsheet with the necessary individuals in my organization.

    Thanks for the VBA idea, I will see if I can get that to work!

  8. #8
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by Joecam View Post
    Thanks for the VBA idea
    You are welcome.
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

Tags for this Thread

Posting Permissions

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