![]() |
#1
|
|||
|
|||
![]()
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... Code:
=ROWS([ReferenceBook.xlsx]ReferenceSheet!$A$1) Code:
=ROWS('C:\My Documents\ReferenceBook.xlsx'!MyRange) If the ref file is open I can also successfully create a formula using a calculated names range such as Code:
=ROWS(ReferenceBook.xlsx!MyOffsetRange) Code:
=ROWS('C:\My Documents\[ReferenceBook.xlsx]ReferenceSheet'!MyOffsetRange) '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 |
Tags |
calculated, named range, reference |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
mjlaw | Excel | 4 | 03-26-2012 10:40 AM |
![]() |
stovaaa | Excel | 4 | 03-09-2012 08:32 AM |
![]() |
jgelpi16 | Excel | 1 | 04-08-2011 03:08 PM |
![]() |
Ziggy-R | Word | 10 | 09-28-2010 01:42 AM |
![]() |
ggh77 | PowerPoint | 2 | 04-16-2010 03:40 AM |