Dynamic Named Range Causes Crash

Hi all,

Anyone run across this.....Over the past 1-2 years...I have setup a number of complex excel workbooks, that automate a lot of reporting for my employees. Suddenly, over the past couple months, I am finding that if I try to go back and edit many of my named ranges (created a while ago) that were created dynamically: "=OFFSET(Sheet1!$A$4,0,0,COUNTA(Sheet1!$A$4:$A$65000),4)"...excel crashes. I can't figure out why.

All I have to do is simply CLICK in the "Refers to:" field of the "Define Name" window (for the dynamically named range)....and I get "Microsoft Office Excel has encountered a problem and needs to close. We are sorry for the inconvenience."

If I drill down on "What is in the report", I see this "Error signature":

AppName: excel.exe AppVer: 11.0.8316.0 AppStamp:4ace3b8f
ModName: excel.exe ModVer: 11.0.8316.0 ModStamp:4ace3b8f
fDebug: 0 Offset: 00086d7a

I can't now edit the named ranges...without excel crashing....every time. This appears to be happening to nearly all my older (1 -2 years) workbooks. I have tried copying the data (in the range refered to) to another worksheet...and recreating the named range, dynamically again......works fine. So, it doesn't appear to be an issue with the data.

Could there be an issue with data, having refreshed so many times...that the dynamic named range gets corrupt, or something? What is strange is that the ranges are correct. I have pivot tables referencing the ranges. They DO properly update. I just can't edit the named range.

Any help is greatly appreciated. I'd hate to think I need to go through ALLLLLLLL my workbooks.....manually overriding the named ranges {if that would even work.}

Thanks,

Keith W.