Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Named Ranges
Hi all,
I have taken over and enhanced an Excel application. It was quite untidy and I have been trying to clean it up to make it easier to read for someone else doing future development. One of the things I would like to do is clean up the convention for named ranges. The workbook is full of names (approx. 100), most of which are not easily identified where they come from, although I know I can use name utilities to identify them (and many thanks to Rob Bovey, Jan Karel Pieterse, and Charles Williams for their excellent utilities). But I would like to make the names more easily recognisable. To give an example, I would like to use a convention "input_Name" for named ranges on the input pages, or "summary_Name" for named ranges on the summary page. (Incidentally, I'm just guessing this is best practice?) However, there seems to be no easy way to go through and bulk change the names, AND make sure the references in the spreadsheet are changed (and of course there are references in VBA). Instead of just being able to alter the name, it seems I have to: • add a new name • go through the workbook and do a find/replace (checking each one to make sure its the correct name to change - eg. the name input_location vs. a title "Location") • delete the old name • go through the VBA and do a find/replace (once again checking each occurrence) Can anyone advise if there is such a way to more easily enable these changes? I would have thought that it should be a simple matter to change the names (at least in the spreadsheets), but I can't find any such method. Many thanks for any advice, Chris Adams |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Named Ranges
Hi Chris,
Instead of just being able to alter the name, it seems I have to: • add a new name • go through the workbook and do a find/replace (checking each one to make sure its the correct name to change - eg. the name input_location vs. a title "Location") • delete the old name • go through the VBA and do a find/replace (once again checking each occurrence) I'm afraid that is the only way right now. Charles Williams has been working on a renaming routine for our Name Manager, but as you can imagine this is rather complex material, especially when doing it in slow business hours <g. I would use the Name Manager in combination with my Flexfind to get this job done. You may find the "List" and Pick up" feature of the NM very useful to quickly create the new names: 1. Click the list button 2. Close the name manager and go to the new list 3. Edit each name's name to create a new name with the same RefersTo string (better yet, first copy the entire table downwards and edit the copied rows so you have overview of the old and new situation) 4. Start Name Manager and hit the Pick-up button and follow instructions to add the new names. Now you have the names named as you need them and have to start finding and replacing them. I'd create a two-column table first, listing old and new names next to each other to keep track. Then I'd use Flexfind to do the S&R, because it enables: 1. S&R in all (well, as far as I know) objects 2. Single entry confirmation within each formula. Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Named Ranges
Jan,
Many thanks for the reply. Yes, I can believe the complexity of Charles' task :). I have just tried Flexfind - a powerful little utility. Really useful indeed, and complements the Name Manager exceptionally well. I'm following your advice below and its working quite well considering the task. Many thanks again for your advice and especially the utilities - very much appreciated. Kind regards, Chris Adams Jan Karel Pieterse wrote in message ... I'm afraid that is the only way right now. Charles Williams has been working on a renaming routine for our Name Manager, but as you can imagine this is rather complex material, especially when doing it in slow business hours <g. I would use the Name Manager in combination with my Flexfind to get this job done. You may find the "List" and Pick up" feature of the NM very useful to quickly create the new names: 1. Click the list button 2. Close the name manager and go to the new list 3. Edit each name's name to create a new name with the same RefersTo string (better yet, first copy the entire table downwards and edit the copied rows so you have overview of the old and new situation) 4. Start Name Manager and hit the Pick-up button and follow instructions to add the new names. Now you have the names named as you need them and have to start finding and replacing them. I'd create a two-column table first, listing old and new names next to each other to keep track. Then I'd use Flexfind to do the S&R, because it enables: 1. S&R in all (well, as far as I know) objects 2. Single entry confirmation within each formula. Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Named Ranges
Hi Chris,
I have just tried Flexfind - a powerful little utility. Really useful indeed, and complements the Name Manager exceptionally well. I'm following your advice below and its working quite well considering the task. Thanks for the compliments. Many thanks again for your advice and especially the utilities - very much appreciated. No thanks! Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamically Changing Named Ranges | Excel Worksheet Functions | |||
Changing named ranges | Excel Discussion (Misc queries) | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
Named Ranges | Excel Programming | |||
named ranges - changing ranges with month selected | Excel Programming |