Dynamic Named Range Causes Crash

[kwilliam10]

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.

 

[dahu]

Hi all,
I had the same problem.
The best workaround i found to solve it was to uninstall an Excel security update : KB973475 (Released on 27 of october 2009.)
I hope it 'll help some of you as it helped me

dahu.

 

[nirod]

Hi guys

Thank you for all your support. I have unstalled the security update and it has fixed all my excel problems. Now I have to do that to all my users... grrr.

Thank you

Niro

 

[kwilliam10]

It's been THREE @#$%$##@#$$$ MONTHS, since that update. I'd LOVE to bill Microsoft for all my lost time. I just had ANOTHER ##@$$%$## worksheet crash, because I clicked into a named cell range...and there was a "Freeze Pane" in effect. I forgot...and lost the past two hours worth of work.

I can't belive I've EVER hated Microsoft more, than I do right now. Where is the FIX???!!!!!! They CERTAINLY have gotten word by now that people are crashing their work...ALL OVER THE WORLD, for crying out loud. Just FIX IT!!

Rant off!

Keith W.

 

[RoryA]

It's probably worth telling them directly - they shouldn't charge for support related to an update.

 

[kwilliam10]

You mean...all those "submit errors" are just going to waste?

Issue is....I'm under the gun as it is....NOW...I have to duplicate two hours worth of work. Who in the world has time to waste...waiting for an hour, on hold....just to talk to some guy from India, who doesn't understand me??

I hear what you are saying....but I can't believe they don't know about it.

I'm just venting. sorry.

Keith W.

 

[RoryA]

No problem - you're not alone and there have been similar feelings aired amongst the MS MVPs!

 

[EAnton781]

God bless Rorya. I owe you a 6-pack