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


.