Links

[Sonnie]

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]

=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.

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?

 

[Sonnie]

Dave:

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

Sonnie

=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.

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?

 

[Sonnie]

Dave:

Thanks for the help. This solves my problem.

=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.

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?