Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 8th 10, 10:19 AM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 60
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   Report Post  
Old January 8th 10, 10:52 PM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 473
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   Report Post  
Old January 9th 10, 09:00 AM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 60
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   Report Post  
Old January 9th 10, 01:48 PM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 473
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Defind Dynamic named Range up to first error chg Excel Worksheet Functions 3 December 6th 09 09:32 PM
Dynamic referencing to files Sungibungi Excel Discussion (Misc queries) 4 November 26th 09 02:19 AM
#value! error trying to create a simple dynamic named range Janis Excel Discussion (Misc queries) 1 August 3rd 07 07:20 PM
Dynamic file referencing? [email protected] Excel Worksheet Functions 1 October 9th 06 09:08 PM
Dynamic Range with unused formula messing up x axis on dynamic graph [email protected] Charts and Charting in Excel 2 February 2nd 06 08:02 PM


All times are GMT +1. The time now is 12:05 AM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017