Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
External reference for Offset formula
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
External reference for Offset formula
I just found my answer - Offset doesn't work with a closed spreadsheet. No
need for anyone to respond. Thanks. -- AJ "AJ" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
External reference for Offset formula
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! ================================================== === "AJ" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change formula to reference different external worksheets | Links and Linking in Excel | |||
OFFSET and external workbook references | Excel Discussion (Misc queries) | |||
Dynamic Reference Cell in Offset Formula | Excel Discussion (Misc queries) | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
Formula Contains an invalid external reference to a worksheet | Setting up and Configuration of Excel |