how to change text in formulas automatically?

[jci_vlt]

Hi there,

i created an excel database with all employees and how much they sell during the month. each emplyee has his own worksheet and the first worksheet is an overall summary of how they are all doing.

my question is, i want to change one of the employees name on the overall summary worksheet, but then i would need to change all the names in the formulas and the name of the employees worksheet. is there anyway this can be done automatically?

eg, if i changed jane smith in the first worksheet to bob smith, is there anyway that i could rename jane smith's worksheet and all the formulas referencing her name to bob smith?

 

[tactps]

If you change the name of the worksheet first, the formulas will change automatically to reflect that.

Then change the person's name in the first worksheet.

 

[tactps]

You can also highlight the area that contains the formulas and use the Edit-Replace function to change the name.

 

[RAM]

Hi,

If you put some work into your workbook and select one cell in each worksheet to be the name cell and refer all formulas to this cell then this code will name the worksheet accordingly to that cell and you only have to change the name in one cell as you assign a worksheet to a new member.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
If Target.Address <> "$A$1" Then Exit Sub
ActiveSheet.Name = Range("A1").Text
End Sub

Change $A$1 in the above code to the cell you will assign. If you later change the cell you need to manually change the code.

Right-click on the tab - View code and past the code in the editor.

Hope This Helps

RAM