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...
and when the ref file is closed...
both work fine.





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

When the Sharepoint file is then closed this formula becomes

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

Just to follow up on this, I have found the following information elsewhere (thanks 'Barry'!) and intend to try out the suggested solution: