OFFSET() function with external references

[braultg]

Could the OFFSET function be used with external references ? Everything
OK when the external workbook is open, but #VALUE is returned when not.
I want OFFSET to work when the other workbook is closed.

Here is an example :

"Workbook1.xls" contains
A1 : ='BOB
B1 : ='ROGER

"Workbook2.xls" contains (with other source opened)
A1 : =OFFSET([Workbook1.xls]Sheet1!A1; 1; 0)

=> Return "ROGER" when workbook1.xls is open
=> Return #VALUE otherwise

Is there any workaround for this problem ? Thanks

 

[braultg]

You should have read :

"Workbook2.xls" contains (with other source opened)
A1 : =OFFSET([Workbook1.xls]Sheet1!A1; 0; 1)

=> Return "ROGER" when workbook1.xls is open
=> Return #VALUE otherwise

 

[Bob Phillips]

Why not just use

=[Workbook1.xls]Sheet1!B1

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[braultg]

Because I want my reference to workbook1 to be dynamic (i.e. the
referenced cell in workbook1 be dependant of a value in workbook2)

Why not just use

=[Workbook1.xls]Sheet1!B1

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

You should have read :

"Workbook2.xls" contains (with other source opened)
A1 : =OFFSET([Workbook1.xls]Sheet1!A1; 0; 1)

=> Return "ROGER" when workbook1.xls is open
=> Return #VALUE otherwise