PDA

View Full Version : Why Does Excel 2007 Erase My Defined Names?



Cyberdude
01-28-2010, 08:49 PM
About 10 years ago I created a set of about 50 formulas each having its own defined name in about 30 Excel 2003 workbooks. The formula for a typical one of these defined names looks like the following:
Defined Name = CurrCellD2P3]
Refers To:
Offset(Indirect(Address(Row(),Column()), 3, 2)
When I started opening my workbooks created with Excel 2003 and containing a set of these formulas with defined names, at some point Excel 2007 removed EVERY occurrence (including the name definitions) from the workbooks. I got no error message anywhere that suggested that Excel 2007 was making this change to my workbooks.
My question is what rule have I violated that caused Excel 2007 to make these disasterous changes??
Sid

RolfJ
01-28-2010, 11:10 PM
Were these named ranges created with a macro? I am probably telling you the obvious, but Excel2007 requires a special extension for workbooks with macros ('Macro-enabled Workbooks': '*.xlsm').

Bob Phillips
01-29-2010, 07:15 AM
I have encountered a problem with Excel wiping out names, but it was usually an odd one here and there, not all of them.

There was also a hotfix for it, I will try and track it down and post it later.

Bob Phillips
01-29-2010, 07:18 AM
Here are those details

Make sure you have Office 2007 updated to at least SP1; visit Microsoft Update (http://support.microsoft.com/kb/968863/en-us) to check if you need it.
After that, visit the hotfix page and get the hotfix download (http://support.microsoft.com/kb/968863/en-us).

Cyberdude
01-30-2010, 12:25 PM
Thanx, XLD. I've run my system through Microsoft Diagnostics (Detect & Repair) several times and always the same results . . . my system is in great shape. Nothing was found wrong or missing or out-of-date. I'll look into the hot fix this afternoon.
Sid