Name Manager

[MadmanZ71]

Is there a way to edit a name in a cell and name manager account for it? Essentially I have data that is valued to certain names and when I change one of the name in the cells the name manager does not account for this change and I have to go into the name manager menu and manually edit the name to match what it has in the cell. I'm hoping there's a way to be able to manually change names and have name manager recognize this change so that the values linked to it still will show up even after the name change.

 

[mikerickson]

I'm not sure what you are doing,
Names are uses so that one can do all the changing in the Name Manager and leave the cell formulas alone.

Could you give us a detailed example of what you are doing and need help with?

 

[MadmanZ71]

Basically what I am doing is taking a ton of information from a "master" sheet on an excel notebook and trying to just select a few rows of information and put it on a seperate sheet in the same notebook. Now obviously a way to do this would be to copy the whole row of information I want and paste special to the seperate sheet but when filtering through the master sheet for the data I want, it is a major pain to deal with. So to make this process easier, I made a drop down menu based off of the names of the data numbers (located in the first column) I have on the master sheet and used the data validation to create drop down menus on the first column of the seperate sheet so I can select the names on the data sheet instead of going back and forth sheet to sheet.

Now in order for the data associated with the names on the list be able to appear on the data sheet I had to create an IF statement and INDIRECT function in each of the cells in the same row as the name I'm choosing from the drop down menu. I also had to use Name Manager to take the values I want from the master sheet and put a name for those values which I matched with names on the drop down list. So, when I choose one of the names that's on the drop down list the data will show up on the data sheet. However, the problem is if I decided I needed to change a name in the first column of the master sheet, the drop down list will account for it but the data will not show up once I pick it. The reason is because the name manager still has it under the old name and does not account for the change. The goal is to be able to make that name change and name manager recognize the change, and my data values will show up without having to edit the name in name manager to once again match the name in the list.

 

[mikerickson]

I still don't understand what you are doing.
What names are you using in what formulas?
What do you want those names to be?

The user should not be changing the name of a Name in mid stream.
Is this a tool you are using for development?

 

[MadmanZ71]

To specify more what I am trying to accomplish, what I am doing is taking setup information on a race car in which I have several years worth of data on a master sheet and trying to pick only a few setups and make a chart off of the information. In order to simplify the chart process I wanted to make a seperate data sheet to only accomodate the certain setups and data associated with them for analysis. The copy and pasting is a pain because you have to go through the large master sheet file and find and pick the setups I need for analysis and then paste on the seperate sheet. So the drop down menu on the data sheet is based off of the setup names (usually named after the track the car has ran on example: Chicago, Kansas, New_Hampshire). So through data validation I made a list based off of those setup names so that I can pick the setup I need. Now with each setup, the data I'm analyzing is found on the same row just the next columns over for each setup. So in order for the data to show up on the data sheet when I choose a setup from the drop down list I had to create the formula =IFERROR(IF(C2="","",INDIRECT(C2)),"") in the cells that are on the same row as the cell with the drop down list so that the data numbers will appear when something is chosen. However, in order to get the whole row of data to show up I had to associate the data values in name manager with the name of the setup. So let's say I pic a row of data for the setup New_Hampshire, I would name the string of values New_Hampshire in name manager and then pic New_Hampshire in the drop down list and all the data numbers will show up. Where the problem comes in is lets say there's a problem and I need to add a specific date to the name of the setup because there are several New_Hampshire setups and have to make it New_Hampshire_9_28_2011, the drop down list will account for the change and show up when looking through the list. However, the name manager still has the data I want labeled as New_Hampshire so the data will not show up when choosing New_Hampshire_9_28_2011. I was hoping there was a way that name manager can recognize the naming change so that I don't have to go back in and edit it myself.

 

[mikerickson]

One workaround would be to change the defintion of OldName to the formula =NewName.

 

[MadmanZ71]

Where would I be able to change the definition of the name in excel. I was thinking the function bar but wouldn't that only change the definition of the cell?

 

[mikerickson]

The Name Manager is where one can change the RefersTo (definition) of an existing Name.

 

[MadmanZ71]

Is it possible to make a macro on the procedure? The only catch is trying to figure out how in the code I can reference the oldtext and newtext by recording the procedure of changing the name manually and then editing it in name manager.