Referencing a named (calculated) range on a remote spreadsheet

[TishyMouse]

Hi

I'm using Excel 2007.

I have a reference spreadsheet ReferenceBook.xlsx stored on a local drive (in reality this will be store on our Sharepoint server, but the same issue happens in either location).

e.g. the reference file is at

C:\My Documents\ReferenceBook.xlsx


There are several names ranges within this workbook, including MyRange=ReferenceSheet!$J$5:$K$8
and


MyOffsetRange=OFFSET(ReferenceSheet!$A$2,0,0,COUNT A(ReferenceSheet!$A$2:$A$1000))

I'd like to be able to reference these ranges from other workbooks, e.g. to create dropdown lists.

I have done some tests as follows:

Whether the reference file is open or closed I can easily create a reference on a local spreadsheet to my reference file a perform functions on the value, e.g.
when the ref file is open...

=ROWS([ReferenceBook.xlsx]ReferenceSheet!$A$1)

and when the ref file is closed...

=ROWS('C:\My Documents\ReferenceBook.xlsx'!MyRange)

both work fine.



If the ref file is open I can also successfully create a formula using a calculated names range such as

=ROWS(ReferenceBook.xlsx!MyOffsetRange)

When the Sharepoint file is then closed this formula becomes

=ROWS('C:\My Documents\[ReferenceBook.xlsx]ReferenceSheet'!MyOffsetRange)

but I get a #REF! error on the cell and if I subsequently select the cell and press return to recalculate the formula I get a message
'Microsoft Office Excel cannot find 'MyOffsetRange' on 'ReferenceBook.xlsx'. etc...

Is there any way round this or is this a fundamental limitation of Excel?

Thanks in advance

TM