String to Formula with reference to external source file

[Mister Madders]

I have two workbooks. One is the master file ("Master") and one is a set of edits ("Edits"). I must QC the Edits to the Master. In the Edits file I have a cell reference and a value/edit that has been made in the master.

I would like to build a formula in the Edits file along the lines of ='[master_file_name.xlsb]tab'!$A$1 that will reference back to the master file and show me the contents of the cell within the Master. Then I can a basic TRUE/FALSE formula to see if the edit is accurate.

I can go this far: =INDIRECT(CONCATENATE("=",$N$1,$D161))
- N1 is the text string for the external file name: '[Master_file_name]tab'!
- D161 is the cell address on 'tab' of Master file: $T$15891
When I try this in the Edits file it will return a #REF! I assume this means it cannot file the tab within the Edits file, but I am not sure how to tell Excel to reference the Master file.
I can get this to work if I manually select the specific cell within the Master, but that is inefficient.

I did go into 'edit links' to make sure the Edits file was linked up to the Master and it does appear to be.

Thanks in advance!

 

[DRSteele]

Welcome to the forum.

I hazard a guess that your formula is referring to a file that is not open. The MS HELP article for function INDIRECT says: if the reference in INDIRECT refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.

 

[Mister Madders]

Welcome to the forum.

I hazard a guess that your formula is referring to a file that is not open. The MS HELP article for function INDIRECT says: if the reference in INDIRECT refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.

Thanks DRSteele. I do have both workbooks open when attempting the external reference. Both files are saved locally.