A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Links and Linking
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Error referencing linked dynamic range



 
 
Thread Tools Display Modes
  #1  
Old January 8th, 2010, 11:19 AM posted to microsoft.public.excel.links
Basil
external usenet poster
 
Posts: 37
Default Error referencing linked dynamic range

Hi,

I'm having trouble with a link I've set up in a calculation.

I have two rather large spreadsheets (Excel 2003 ~ 10MB each):
A (MASTER Pricing.xls) and B (Myfile.xls).

In A I have a named range (Tier_Data) that uses the offset formula and
counta in order to be dynamic (if you add any rows or columns, the range
expands).

In B I have a formula that looks values up from the range. eg:
=VLOOKUP($C11,'C:\MASTER Pricing.xls'!Tier_Data,2,0)

This works beautifully when both workbooks are open, however if I do not
open A and try to update links or calculate cells in B, it gives me an error.

Updating gives me 'Error: Undefined or non-ractangular name'
Calculating says it cannot find 'Tier_Data' on 'MASTER Pricing.xls'

Can someone please advise on how to get around this problem please?

Many thanks,

Basil
  #2  
Old January 8th, 2010, 11:52 PM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default Error referencing linked dynamic range

I think the only solution is to have both workbooks open.
You could have a macro in B open A read-only and make it invisible if
that helped.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

  #3  
Old January 9th, 2010, 10:00 AM posted to microsoft.public.excel.links
Basil
external usenet poster
 
Posts: 37
Default Error referencing linked dynamic range

Thanks Bill,

That was my back-up plan. It's a bit unfortunate as I actually
underestimated the problem - there are actually 2 other linked files - one is
20MB the other is 10MB and both have some hefty calculations in them. This
means that calculating/saving whilst they are open takes ages.

Do you know what causes this issue? If it was a standard range reference
(rather than named range) would this happen? Or is is the fact that the named
range is calculated?

Thanks,

Basil

"Bill Manville" wrote:

I think the only solution is to have both workbooks open.
You could have a macro in B open A read-only and make it invisible if
that helped.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

.

  #4  
Old January 9th, 2010, 02:48 PM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default Error referencing linked dynamic range

I would think it is the fact that the named range is dynamic.
You could put a Before_Save macro in the source workbook to define a
static range name equal to address the current range of Tier_Data.

That might well work - but the circumstances that make formulas fail to
work from closed workbooks are far from clear. If you think about it,
Excel has rather a lot to do to compute a VLOOKUP on a closed workbook.
Sometimes I am surprised that it works at all!

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:02 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.