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

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

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

.

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