Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #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
  #2  
Old 04-25-2012, 08:45 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
Default

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

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

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 09:24 PM.


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