View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
Bob Berens[_2_] Bob Berens[_2_] is offline
external usenet poster
 
Posts: 3
Default Help! Names defined in Excel 2003 invalid in Excel 2007

Dave:

Ah, things are not what I'd thunk they we 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" wrote:

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 wrote:

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