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

=IF(ISBLANK(B26)=TRUE,"",VLOOKUP(B26,Masterprice_C ONFIDENTIAL.xls!all_prices,MATCH(which_price,Maste rprice_CONFIDENTIAL.xls!plist_code,0),FALSE))

The named range all_prices refers to
=OFFSET(Prices!$A$1,0,0,MATCH("*",Prices!$A$1:$A$1 5000,-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))

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

--
Biff
Microsoft Excel MVP


"Code Numpty" wrote in message
...
I have a template that references data in another workbook with the
following
formula.

=IF(ISBLANK(B26)=TRUE,"",VLOOKUP(B26,Masterprice_C ONFIDENTIAL.xls!all_prices,MATCH(which_price,Maste rprice_CONFIDENTIAL.xls!plist_code,0),FALSE))

The named range all_prices refers to
=OFFSET(Prices!$A$1,0,0,MATCH("*",Prices!$A$1:$A$1 5000,-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?

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" wrote:

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

--
Biff
Microsoft Excel MVP


"Code Numpty" wrote in message
...
I have a template that references data in another workbook with the
following
formula.

=IF(ISBLANK(B26)=TRUE,"",VLOOKUP(B26,Masterprice_C ONFIDENTIAL.xls!all_prices,MATCH(which_price,Maste rprice_CONFIDENTIAL.xls!plist_code,0),FALSE))

The named range all_prices refers to
=OFFSET(Prices!$A$1,0,0,MATCH("*",Prices!$A$1:$A$1 5000,-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?


.

=$A$1:INDEX($A$1:$D$100,COUNTA($A$1:$A$100),COUN TA($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))

--
Biff
Microsoft Excel MVP


"Code Numpty" wrote in message
...
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" wrote:

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

--
Biff
Microsoft Excel MVP


"Code Numpty" wrote in message
...
I have a template that references data in another workbook with the
following
formula.

=IF(ISBLANK(B26)=TRUE,"",VLOOKUP(B26,Masterprice_C ONFIDENTIAL.xls!all_prices,MATCH(which_price,Maste rprice_CONFIDENTIAL.xls!plist_code,0),FALSE))

The named range all_prices refers to
=OFFSET(Prices!$A$1,0,0,MATCH("*",Prices!$A$1:$A$1 5000,-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?


.

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.

Sorry, I'm out of ideas.

--
Biff
Microsoft Excel MVP


"Code Numpty" wrote in message
...
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!!

"T. Valko" wrote:

Sorry, I'm out of ideas.

--
Biff
Microsoft Excel MVP


"Code Numpty" wrote in message
...
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.


.

Good luck!

--
Biff
Microsoft Excel MVP


"Code Numpty" wrote in message
...
Thanks for trying. I've gone back to making it a named range and will just
have to remember to update it!!

"T. Valko" wrote:

Sorry, I'm out of ideas.

--
Biff
Microsoft Excel MVP


"Code Numpty" wrote in message
...
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.


.

On Tue, 23 Feb 2010 11:00:20 -0500, "T. Valko"
wrote:

Sorry, I'm out of ideas.


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