Need to replace a portion of a formula in a range of cells

[DEB]

Anyone know how to replace a portion of a formula in a
range of cells? i.e. I want to replace a range of cells
that have the same formula but have different sheet
names... so... for instance i want to change all the cells
in range B53:M69 from =Tina!$E$45, etc... to =Nelson!$E$45

Is there an easy way to do that?? I have about 100
worksheets with a summary sheet and all the worksheets are
identical but will have differing data entered depending
on the employee info entered... right now I am doing one
cell at a time. There MUST be a better way! argh! heh...

thanks!! =)

Deb

 

[mzehr]

Hi Deb
You can select the sheets you want to perform the action
on, and select the grey box at the upper left hand corner
of the worksheet where the rows and columns intersect to
select all cells in the worksheet, and use find and
replace to find Tina! and replace with Nelson!

HTH

 

[Guest]

I tried that to begin with... it wont replace tina with
Nelson... I thought it was because it was in a formula...
works fine with text fields or values, but cant get it to
do that with a formula...

 

[mzehr]

Deb,
In the find and replace box, was the Look In: Formulas
option selected?

HTH

 

[DEB]

Well that is a very interesting question, because I dont
have a look in field on my find/replace dialogue box to
choose formulas from... and there is no "more" button to
click to enable me to view more functions... is there an
option somewhere for me to enable that? or did our sys
admin disable that function for some reason maybe... hmm...

 

[Guest]

Deb,
I'm not sure what version of Excel you are using, but
XL2000 when you first bring up the Find Box CTL+F,
the first screen (Find) would be where you would put in
what you want to Find: Tina!
the next line down would be how you want to Search:, by
rows or by columns,
then the third line down would be where you Look In: by
Formulas, Values, or Comments.
Make sure that the Match Case, and the Find Entire Cells
Only options are deselected!

Once you select those variables you can select the Replace
Button, which takes you to the Replace Box where you would
input your new replacement variable. Again, make sure
that the Mathe Match Case, and the Find Entire Cells Only
options are deselected!

Hope this gets you there