Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 10th 09, 10:32 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2009
Posts: 1
Default Help! Names defined in Excel 2003 invalid in Excel 2007

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?

  #2   Report Post  
Old July 10th 09, 10:56 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default Help! Names defined in Excel 2003 invalid in Excel 2007

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
  #3   Report Post  
Old July 10th 09, 11:34 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2009
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

  #4   Report Post  
Old July 11th 09, 12:50 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default Help! Names defined in Excel 2003 invalid in Excel 2007

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.



Bob Berens wrote:

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


--

Dave Peterson


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2007. Self Digital Signature invalid, can't del. Bill R Excel Discussion (Misc queries) 1 August 2nd 11 11:10 AM
Invalid web query Excel 2007 Bob Excel Discussion (Misc queries) 0 June 26th 09 03:51 PM
2007 Defined Names Is Locked! [email protected] Excel Worksheet Functions 2 June 16th 09 08:36 PM
MS Excel, Location: Defined Names [email protected] Excel Worksheet Functions 1 September 17th 08 07:50 AM
display defined names in excel Mike C Excel Discussion (Misc queries) 3 March 14th 07 06:51 PM


All times are GMT +1. The time now is 12:47 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017