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

[TishyMouse]

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

Dynamic Named Ranges have to be "Calculated" hence the need for the workbook to be open. A workbook can only be calculated when open.

However, how can you get around this. There is a kluge way around it. What you need to do is when the target workbook is open that before it closes it puts the address of the named range into a cell as a fixed value.

You should then be able to reference that address and retrieve the value and use it to in place of the dynamic named range.