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.

 

[Jeffrey Mahoney]

Are there cells in your dynamic named range that are referencing external workbooks?

 

[ohnow]

Are there cells in your dynamic named range that are referencing external workbooks?

Thank you for responding! There are not that I know of.

 

[ohnow]

bump

 

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

I'm sorry to bump this but this issue is causing me to open all of my linked workbooks which causes me to wait for Excel to finish calculating after each action I take and the linked workbook cells will show errors instead of results. I could turn on manual calculations but I would still have the error issue. Thank you for understanding.

 

[Jeffrey Mahoney]

Can you please try this formula. It will the last number or alpha in column C and the last number or alpha in row 9. If you need the range to exclude any rows at the bottom or any columns to the right, then edit the text in red

I make it a point not to name my sheets with an apostrophe. For some reason my formulas get fouled up. So I renamed the sheet "All POs". I'm thinking that maybe your sheet name is not right in the formula and it forces it to think you are looking for an external workbook.

=OFFSET('All POs'!$C$9,0,0,LOOKUP(9.99+307,SEARCH("?*",'All POs'!C:C),ROW('All POs'!C:C))-8,LOOKUP(9.99+307,SEARCH("?*",'All POs'!9:9),COLUMN('All POs'!9:9))-2)

 

[ohnow]

Thanks for the reply Jeffrey. You're right about the apostrophe. I try not to use it too but this one slipped by me. To remove the possible apostrophe issues I looked at a different dynamic named range. When I tried your formula and had both workbooks opened, my vlookups returned errors instead of values. Switching back to my old formula returned values again.

The name of the named range is "LU_POProd_LUKey". My original formula was:
=OFFSET('PO Product Search'!$C$9,0,0,COUNTA('PO Product Search'!$A:$A)-1,COUNTA('PO Product Search'!$9:$9)-2)

I changed it to your formula:
=OFFSET('PO Product Search'!$C$9,0,0,LOOKUP(9.99+307,SEARCH("?*",'PO Product Search'!C:C),ROW('PO Product Search'!C:C))-8,LOOKUP(9.99+307,SEARCH("?*",'PO Product Search'!9:9),COLUMN('PO Product Search'!9:9))-2)

I used these dynamic ranges to allow data to expand or retract before Excel introduced Tables. I just researched using Tables for a possible solution but the MS help site said the file being linked to has to be opened or errors will exist. So, perhaps there is no solution for this.

Thanks for trying to help! I really appreciate it.

 

[Jeffrey Mahoney]

When you say you had both workbooks open, what do you mean. I believe that named ranges can only be created within a workbook.

I tested the formula inside a cell and it looked at the right range. I did alter it a little because I forgot to anchor the ranges:
=OFFSET('All POs'!$C$9,0,0,LOOKUP(9.99+307,SEARCH("?*",'All POs'!$C:$C),ROW('All POs'!$C:$C))-8,LOOKUP(9.99+307,SEARCH("?*",'All POs'!$9:$9),COLUMN('All POs'!$9:$9))-2)

 

[ohnow]

When you say you had both workbooks open, what do you mean. I believe that named ranges can only be created within a workbook.

I tested the formula inside a cell and it looked at the right range. I did alter it a little because I forgot to anchor the ranges:
=OFFSET('All POs'!$C$9,0,0,LOOKUP(9.99+307,SEARCH("?*",'All POs'!$C:$C),ROW('All POs'!$C:$C))-8,LOOKUP(9.99+307,SEARCH("?*",'All POs'!$9:$9),COLUMN('All POs'!$9:$9))-2)

I'm using two workbooks called Buy Plan (this holds the source data and dynamic named range) and Inventory Valuation (which has the vlookup to the Buy Plan). It's when I open the Valuation file that any cells with a vlookup to the Buy Plan named range using the dynamic formula will return an error. When I open the Buy Plan source workbook the vlookups in the Valuation will update.

When I used your previous formula and had both workbooks open, I received errors rather than the correct values. I've tried the new formula above and having both workbooks open returns values. When I close both workbooks, then open the Valuation I have the original problem that links will not update. I have to also open the Buy Plan file otherwise I get errors.

Thanks again for your efforts on this!

 

[Jeffrey Mahoney]

Are all of your values in the dynamic named range going to be values and NOT text cells? If so then this will work:

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