Links to Dynamic Named Range = Problem

  • Thread starter Thread starter Code Numpty
  • Start date Start date
C

Code Numpty

I have a template that references data in another workbook with the following
formula.

=IF(ISBLANK(B26)=TRUE,"",VLOOKUP(B26,Masterprice_CONFIDENTIAL.xls!all_prices,MATCH(which_price,Masterprice_CONFIDENTIAL.xls!plist_code,0),FALSE))

The named range all_prices refers t
=OFFSET(Prices!$A$1,0,0,MATCH("*",Prices!$A$1:$A$15000,-1),MATCH("*",Prices!$1:$1,-1))

When I open a new file based on the template I have to edit the links
because I am getting 'Error: Undefined or non-rectangular name'.

When I click on Open Source the linked file opens OK and when I go back to
the original the links have updated and the edit links dialog box has gone.

My formula results in #REF unless the linked file is actually open.

How can I stop this from happening? Is there some kind of problem with my
dynamic named range?
 
OFFSET doesn't work on closed files.

You'd need to redefine:
=OFFSET(Prices!$A$1,0,0,MATCH("*",Prices!$A$1:$A$15000,-1),MATCH("*",Prices!$1:$1,-1))
Click to expand...

Without using the OFFSET function.

You can do it using INDEX. Something along the lines of:

=$A$1:INDEX($A$1:$D$100,COUNTA($A$1:$A$100),COUNTA($A$1:$D$1))
 
Thanks Biff,

Unfortunately your helpful suggestion uses COUNT. This won't work because
there are empty cells within the range. I'll keep looking at it tomorrow and
if you have any more advice in the meantime I'm most grateful to read it.

Cheers

Sharon
 
=$A$1:INDEX($A$1:$D$100,COUNTA($A$1:$A$100),COUNTA($A$1:$D$1))
Unfortunately your helpful suggestion uses COUNT.
there are empty cells within the range.
Click to expand...

Then just replace the COUNTs with your MATCHs:

=$A$1:INDEX($A$1:$D$100,MATCH("*",$A$1:$A$100,-1),MATCH("*",$A$1:$D$1,-1))
 
Thanks Biff
My range now refers to

=Prices!$A$1:INDEX(Prices!$A$1:$Z$10000,MATCH("*",Prices!$A$1:$A$10000,-1),MATCH("*",Prices!$A$1:$Z$1,-1))

and works perfectly.

However, I'm still getting the links cannot be updated message.
 
Thanks for trying. I've gone back to making it a named range and will just
have to remember to update it!!
 
Good luck!

--
Biff
Microsoft Excel MVP


Code Numpty said:
Thanks for trying. I've gone back to making it a named range and will just
have to remember to update it!!
Click to expand...
 
Back
Top