String to Formula with reference to external source file

Mister Madders

New Member
Joined
Mar 30, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,381
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 30, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,791
Messages
5,638,343
Members
417,021
Latest member
moon miner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top