Links to Dynamic Named Range = Problem

[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?

 

[T. Valko]

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))

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))

 

[Code Numpty]

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

 

[T. Valko]

=$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.

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))

 

[Code Numpty]

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.

 

[T. Valko]

Sorry, I'm out of ideas.

 

[Code Numpty]

Thanks for trying. I've gone back to making it a named range and will just
have to remember to update it!!

 

[T. Valko]

Good luck!

--
Biff
Microsoft Excel MVP

Thanks for trying. I've gone back to making it a named range and will just
have to remember to update it!!

 

[CellShocked]

Sorry, I'm out of ideas.

He may need to save, exit, and re-open the file.