Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007. Self Digital Signature invalid, can't del. | Excel Discussion (Misc queries) | |||
Invalid web query Excel 2007 | Excel Discussion (Misc queries) | |||
2007 Defined Names Is Locked! | Excel Worksheet Functions | |||
MS Excel, Location: Defined Names | Excel Worksheet Functions | |||
display defined names in excel | Excel Discussion (Misc queries) |