Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.links
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Defind Dynamic named Range up to first error | Excel Worksheet Functions | |||
Dynamic referencing to files | Excel Discussion (Misc queries) | |||
#value! error trying to create a simple dynamic named range | Excel Discussion (Misc queries) | |||
Dynamic file referencing? | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel |