Help! Names defined in Excel 2003 invalid in Excel 2007

[Bob Berens]

I've created a number of spreadsheets in previous versions of Excel that
employ named ranges with the format "Jnnnnnn" (where "nnnnnn" is a six-digit
number). This is now a valid cell address for things way down in the 10th
column. When I saved the workbook in the new XLSX format, Excel tried to
change all references to such names as "_Jnnnnnnn", but didn't change the
"Named Range" names accordingly -- my thousands (literally) of
formerly-working formulas all recalculated to "#REF". I've also got tons of
other workbooks that use link to these named ranges, which also now don't
work.

Is there a way to globally change the names of named ranges, in a manner
similar (or not) to the global search/replace function for actual cell
contents?

 

[Dave Peterson]

I don't understand.

Didn't xl2007 change the invalid names J### to _J###?

If your formulas still point at names like J###, why can't you do edit|replace?

You'll have to do the same thing in all the other workbooks, too.

 

[Bob Berens]

Dave:

Ah, things are not what I'd thunk they were: Everything blew up because all
my references were kludged together on the fly, concatenating tab names with
range names built up from calculated values. Where it could, Excel changed
both the reference and the name of the named range correctly, but, of course,
it couldn't have figured out that all my "J"s needed to be changed to "_J"s
in formulas like:

=OFFSET(INDIRECT("1989!J"&RC1),0,7,1,1) [RC1 contains 6-digit number]
^

I need to hand-correct the formulas to read:

=OFFSET(INDIRECT("1989!_J"&RC1),0,7,1,1)
^^

Thanks for the quick response, and kick-in-the-pants to re-evaluate the
problem.

:Bob

 

[Dave Peterson]

If you're lucky, you may not have many names and you could edit|replace:

'1989'!J
with
'1989'!_J

But I don't know of anything that will look inside those =indirect()
concatenated text strings.

I hope you dont have anything like:
=indirect("'1989'!"&char(74)&...
or
=indirect("'1989'!"&if(a1="x","J","K")&...

Some tools that may help...

Jan Karel Pieterse's (with Charles Williams and Matthew
Henson) Name Manager:
NameManager.Zip from http://www.oaltd.co.uk/mvp

Bill Manville's FindLink program:
http://www.oaltd.co.uk/MVP/Default.htm

Remember that ctrl-`
(ctrl-backquote (the key to the left of the 1/! on my USA keyboard)
is the shortcut key to show formulas or values.
(tools|options|view tab|Check Formulas is the xl2003 menu way)

Remember to make plenty of backups (save as a new name after you've fixed a few
of them. Disk space is cheap. Time and sanity is expensive!

Good luck.

Dave:

Ah, things are not what I'd thunk they were: Everything blew up because all
my references were kludged together on the fly, concatenating tab names with
range names built up from calculated values. Where it could, Excel changed
both the reference and the name of the named range correctly, but, of course,
it couldn't have figured out that all my "J"s needed to be changed to "_J"s
in formulas like:

=OFFSET(INDIRECT("1989!J"&RC1),0,7,1,1) [RC1 contains 6-digit number]
^

I need to hand-correct the formulas to read:

=OFFSET(INDIRECT("1989!_J"&RC1),0,7,1,1)
^^

Thanks for the quick response, and kick-in-the-pants to re-evaluate the
problem.

:Bob

I don't understand.

Didn't xl2007 change the invalid names J### to _J###?

If your formulas still point at names like J###, why can't you do edit|replace?

You'll have to do the same thing in all the other workbooks, too.