Dynamic Named Ranges Will Not Update

[ohnow]

Hello,
I'm using Excel 2010. I use dynamically named ranges to grow and shrink as the data grows and shrinks. An example is here:

=OFFSET('All PO''s'!$C$9,0,0,COUNTA('All PO''s'!$C:$C)-3,COUNTA('All PO''s'!$9:$9)-2)

When opening a workbook linking to another workbook with one of these dynamically named ranges, I get an error in the Data-Edit Links window for that workbook saying "Error: Undefined or non-rectangular name". So, the links will not update. If I open the linked workbook, the values will update.

Does anyone know if there is a resolution for this? Thanks in advance!

P.S. I don't know why the below verbiage is there and cannot find a way to delete it or this post. Please ignore it.

 

[ohnow]

The named ranges are almost always a mix of text and numeric data in cells. For example a column of inventory part numbers (alpha, numeric and other characters) and a column of qty on hand (numeric) or perhaps a column of vendor names and a column of zip codes.

 

[Jeffrey Mahoney]

Ok if this doesn't work for you then I'm out of suggestions

=OFFSET('All Pos'!$C$9,0,0,MAX(IFERROR(MATCH(1E+300,'All Pos'!$C:$C),0),IFERROR(MATCH("zzzzzzzzzzzzz",'All Pos'!$C:$C),0))-ROW('All Pos'!$C$8),MAX(IFERROR(MATCH(1E+300,'All Pos'!$9:$9),0),IFERROR(MATCH("zzzzzzzzzz",'All Pos'!$9:$9),0))-COLUMN('All Pos'!$B$9))