External reference for Offset formula

[AJ]

I have created an offset formula linked to an external spreadsheet. When the
external spreadsheet is open, the information is updated and is correct.
When I close the external spreadsheet, the cell changes to "#value".

=OFFSET('T:\Operations Finance\Hyperion\Statistics\Data Actuals\[Manual
Statistics_CY.xls]OAKS'!$A5,0,$B$4)/100

Can the offset formula be used with an external spreadsheet reference?

 

[AJ]

I just found my answer - Offset doesn't work with a closed spreadsheet. No
need for anyone to respond.

Thanks.

 

[RagDyeR]

Offset(), like Index(), Sumif(), and several other functions *cannot* be
used to return data from *closed* WBs.

You might try Index(0 for your case.

The cell references are a little different since there is *no* offset, but
an actual location reference, starting at the top, left.

If your old formula was:
=OFFSET('T:\Operations Finance\Hyperion\Statistics\Data Actuals\[Manual
Statistics_CY.xls]OAKS'!$A5,1,2)/100

The Index equivalent (guessing at a range reference of A5 to Z100), might
be:

=Index('T:\Operations Finance\Hyperion\Statistics\Data Actuals\[Manual
Statistics_CY.xls]OAKS'!A5:Z100,2,3)/100


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================




I have created an offset formula linked to an external spreadsheet. When
the
external spreadsheet is open, the information is updated and is correct.
When I close the external spreadsheet, the cell changes to "#value".

=OFFSET('T:\Operations Finance\Hyperion\Statistics\Data Actuals\[Manual
Statistics_CY.xls]OAKS'!$A5,0,$B$4)/100

Can the offset formula be used with an external spreadsheet reference?