Reference Cell in External Spreadsheet Reference String

Joecam

Member
Joined
May 22, 2014
Messages
41
Reaction score
0
Points
6
Excel Version(s)
365
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?
 
Perhaps try the INDIRECT function like =INDIRECT("First_part_of_reference"&A2&"2nd_part_of_your_reference"
 
That didn't work. I am still getting an error.
 
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?
 

Attachments

  • Master.xlsx
    10.4 KB · Views: 7
Last edited:
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?
 
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?
 
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!
 
Back
Top