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?

=offset() doesn't work with closed workbooks.

Maybe you could use =index(match()) instead.

You could look at Debra Dalgleish's site:
http://www.contextures.com/xlFunctions03.html
for some tips.

Sonnie wrote:

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?

--

Dave Peterson

Dave:

Thanks--I'll give it a try and let you know if it works.

Sonnie

"Dave Peterson" wrote:

=offset() doesn't work with closed workbooks.

Maybe you could use =index(match()) instead.

You could look at Debra Dalgleish's site:
http://www.contextures.com/xlFunctions03.html
for some tips.

Sonnie wrote:

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?

--

Dave Peterson

Dave:

Thanks for the help. This solves my problem.

"Dave Peterson" wrote:

=offset() doesn't work with closed workbooks.

Maybe you could use =index(match()) instead.

You could look at Debra Dalgleish's site:
http://www.contextures.com/xlFunctions03.html
for some tips.

Sonnie wrote:

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?

--

Dave Peterson