I have an external cell reference in an OFFSET function in cell A1 of the
workbook named J:\Strategic\Balanced Scorecard\BSC Reports\[Performance
Summary.xls] that contains the following code:

=UPPER(OFFSET('J:\Strategic\Balanced Scorecard\BSC Reports\Client\[AR -
Profit by Segment - Quarterly.xls]Data
Entry'!$A$8,(MATCH('J:\Strategic\Balanced Scorecard\BSC Reports\Client\[AR -
Profit by Segment - Quarterly.xls]Report Page'!$C$3,'J:\Strategic\Balanced
Scorecard\BSC Reports\Client\[AR - Profit by Segment - Quarterly.xls]Data
Entry'!$A$8:$A$82,0)-1),7,1,1))


This function returns the value of a cell using the MATCH function (matching
the report date (€˜J:\Strategic\Balanced Scorecard\BSC Reports\Client\[AR €“
Profit by Segment €“ Quarterly.xls]Report Page!$C$3) to the corresponding
date where data values are stored (€˜J:\Strategic\Balanced Scorecard\BSC
Reports\Client\[AR €“ Profit by Segment €“ Quarterly.xls]Data
Entry!$A$8,$A$82,0)

The correct value is retrieved when the workbook J:\Strategic\Balanced
Scorecard\BSC Reports\Client\[AR €“ Profit by Segment €“ Quarterly.xls] is
open, however, when the workbook is closed, I get a #VALUE error. Since I
will have approximately 50 similar formulas, all referencing different
workbooks, I do not want to have to individually open each workbook. Any
ideas on how to get this link to work when the source file(s) is closed?