Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #1  
Old 04-24-2012, 07:54 AM
TishyMouse TishyMouse is offline Referencing a named (calculated) range on a remote spreadsheet Windows XP Referencing a named (calculated) range on a remote spreadsheet Office 2007
Novice
Referencing a named (calculated) range on a remote spreadsheet
 
Join Date: Feb 2012
Posts: 22
TishyMouse is on a distinguished road
Question Referencing a named (calculated) range on a remote spreadsheet

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)
and when the ref file is closed...
Code:
=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

Code:
=ROWS(ReferenceBook.xlsx!MyOffsetRange)
When the Sharepoint file is then closed this formula becomes

Code:
=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
Reply With Quote
 

Tags
calculated, named range, reference



Similar Threads
Thread Thread Starter Forum Replies Last Post
Referencing a named (calculated) range on a remote spreadsheet Excel - move with tab through named range mjlaw Excel 4 03-26-2012 10:40 AM
Referencing a named (calculated) range on a remote spreadsheet Add _ based on a calculated colums stovaaa Excel 4 03-09-2012 08:32 AM
Referencing a named (calculated) range on a remote spreadsheet Named range drop-down jgelpi16 Excel 1 04-08-2011 03:08 PM
Referencing a named (calculated) range on a remote spreadsheet Calculated dates. Ziggy-R Word 10 09-28-2010 01:42 AM
Referencing a named (calculated) range on a remote spreadsheet PowerPoint Remote Control ggh77 PowerPoint 2 04-16-2010 03:40 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:38 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft