I highlighted a column that contained a simple formula in a very large
spreadsheet. I used REPLACE function to replace the cell locations in the
formula so that I didn't have to re-type the formula with the new cell
locations.

I wanted to change the formula from =SUM(S6:S20). I wanted to change the
formula to =SUM(C6:C20). I used REPLACE to do this before.

THIS time, the REPLACE function changed cells - formulas and text - outside
of my highlight resulting in ALL of the S's on the spreadsheet to a C. ex.
the word submit was changed to "cubmit" and any formulas with a S cell
location changed to a C cell location.

any thoughts?

thank you in advance! jane

I could not replicate your results.

If, somehow, you neglected to highlight a range and had just one cell
selected, Excel would then have carried out the replacement on the entire
worksheet. You have to have at least two cells selected for the replacement
to be restricted to the selection.

--

Vasant


"Jane" wrote in message
...
I highlighted a column that contained a simple formula in a very large
spreadsheet. I used REPLACE function to replace the cell locations in the
formula so that I didn't have to re-type the formula with the new cell
locations.

I wanted to change the formula from =SUM(S6:S20). I wanted to change the
formula to =SUM(C6:C20). I used REPLACE to do this before.

THIS time, the REPLACE function changed cells - formulas and text -
outside
of my highlight resulting in ALL of the S's on the spreadsheet to a C.
ex.
the word submit was changed to "cubmit" and any formulas with a S cell
location changed to a C cell location.

any thoughts?

thank you in advance! jane

Hi!

Why not just reverse the Find/Replace operation: Find "C" replace with "S".

Messing with the registry and changing undo levels costs memory!

Another possible option: Close the file without saving

Biff

"Jane" wrote in message
...
I highlighted a column that contained a simple formula in a very large
spreadsheet. I used REPLACE function to replace the cell locations in the
formula so that I didn't have to re-type the formula with the new cell
locations.

I wanted to change the formula from =SUM(S6:S20). I wanted to change the
formula to =SUM(C6:C20). I used REPLACE to do this before.

THIS time, the REPLACE function changed cells - formulas and text -
outside
of my highlight resulting in ALL of the S's on the spreadsheet to a C.
ex.
the word submit was changed to "cubmit" and any formulas with a S cell
location changed to a C cell location.

any thoughts?

thank you in advance! jane

Some Cs which were originally there will also change to S and hence this will
not work.

Alok Joshi

"Biff" wrote:

Hi!

Why not just reverse the Find/Replace operation: Find "C" replace with "S".

Messing with the registry and changing undo levels costs memory!

Another possible option: Close the file without saving

Biff

"Jane" wrote in message
...
I highlighted a column that contained a simple formula in a very large
spreadsheet. I used REPLACE function to replace the cell locations in the
formula so that I didn't have to re-type the formula with the new cell
locations.

I wanted to change the formula from =SUM(S6:S20). I wanted to change the
formula to =SUM(C6:C20). I used REPLACE to do this before.

THIS time, the REPLACE function changed cells - formulas and text -
outside
of my highlight resulting in ALL of the S's on the spreadsheet to a C.
ex.
the word submit was changed to "cubmit" and any formulas with a S cell
location changed to a C cell location.

any thoughts?

thank you in advance! jane

Hi!

It's my understanding that this is what happened to begin with.

The OP wanted to replace some "S's" with "C's" and *ALL* of them were
changed. Why not just reverse that to get things back to where they were?

Biff

"Alok" wrote in message
...
Some Cs which were originally there will also change to S and hence this
will
not work.

Alok Joshi

"Biff" wrote:

Hi!

Why not just reverse the Find/Replace operation: Find "C" replace with
"S".

Messing with the registry and changing undo levels costs memory!

Another possible option: Close the file without saving

Biff

"Jane" wrote in message
...
I highlighted a column that contained a simple formula in a very large
spreadsheet. I used REPLACE function to replace the cell locations in
the
formula so that I didn't have to re-type the formula with the new cell
locations.

I wanted to change the formula from =SUM(S6:S20). I wanted to change
the
formula to =SUM(C6:C20). I used REPLACE to do this before.

THIS time, the REPLACE function changed cells - formulas and text -
outside
of my highlight resulting in ALL of the S's on the spreadsheet to a C.
ex.
the word submit was changed to "cubmit" and any formulas with a S cell
location changed to a C cell location.

any thoughts?

thank you in advance! jane

Say a formula (=C3+C4) was already present before this across the board
change from Ss to Cs took place. When you change Cs to Ss this formula will
also change to =S3+S4.

Alok Joshi

"Biff" wrote:

Hi!

It's my understanding that this is what happened to begin with.

The OP wanted to replace some "S's" with "C's" and *ALL* of them were
changed. Why not just reverse that to get things back to where they were?

Biff

"Alok" wrote in message
...
Some Cs which were originally there will also change to S and hence this
will
not work.

Alok Joshi

"Biff" wrote:

Hi!

Why not just reverse the Find/Replace operation: Find "C" replace with
"S".

Messing with the registry and changing undo levels costs memory!

Another possible option: Close the file without saving

Biff

"Jane" wrote in message
...
I highlighted a column that contained a simple formula in a very large
spreadsheet. I used REPLACE function to replace the cell locations in
the
formula so that I didn't have to re-type the formula with the new cell
locations.

I wanted to change the formula from =SUM(S6:S20). I wanted to change
the
formula to =SUM(C6:C20). I used REPLACE to do this before.

THIS time, the REPLACE function changed cells - formulas and text -
outside
of my highlight resulting in ALL of the S's on the spreadsheet to a C.
ex.
the word submit was changed to "cubmit" and any formulas with a S cell
location changed to a C cell location.

any thoughts?

thank you in advance! jane

Ok, I see what you mean.

Biff

"Alok" wrote in message
...
Say a formula (=C3+C4) was already present before this across the board
change from Ss to Cs took place. When you change Cs to Ss this formula
will
also change to =S3+S4.

Alok Joshi

"Biff" wrote:

Hi!

It's my understanding that this is what happened to begin with.

The OP wanted to replace some "S's" with "C's" and *ALL* of them were
changed. Why not just reverse that to get things back to where they were?

Biff

"Alok" wrote in message
...
Some Cs which were originally there will also change to S and hence
this
will
not work.

Alok Joshi

"Biff" wrote:

Hi!

Why not just reverse the Find/Replace operation: Find "C" replace with
"S".

Messing with the registry and changing undo levels costs memory!

Another possible option: Close the file without saving

Biff

"Jane" wrote in message
...
I highlighted a column that contained a simple formula in a very
large
spreadsheet. I used REPLACE function to replace the cell locations
in
the
formula so that I didn't have to re-type the formula with the new
cell
locations.

I wanted to change the formula from =SUM(S6:S20). I wanted to
change
the
formula to =SUM(C6:C20). I used REPLACE to do this before.

THIS time, the REPLACE function changed cells - formulas and text -
outside
of my highlight resulting in ALL of the S's on the spreadsheet to a
C.
ex.
the word submit was changed to "cubmit" and any formulas with a S
cell
location changed to a C cell location.

any thoughts?

thank you in advance! jane

since this was a large data base, Hopefully you did not save it after the
replace problem happened so that you did not lose too much. and could restart
from an older file.

I would watch this file. Unless you did one of the things discussed in the
other responses, such as only having one cell selected, it may indicate the
the file is getting corrupted.

I have had large active files get corrupted, and once they start they seldom
just have one problem. I use this as an excuse to regenerate from scratch,
when possible, my important files every so often (of course improving them
when I can.)

"Jane" wrote:

I highlighted a column that contained a simple formula in a very large
spreadsheet. I used REPLACE function to replace the cell locations in the
formula so that I didn't have to re-type the formula with the new cell
locations.

I wanted to change the formula from =SUM(S6:S20). I wanted to change the
formula to =SUM(C6:C20). I used REPLACE to do this before.

THIS time, the REPLACE function changed cells - formulas and text - outside
of my highlight resulting in ALL of the S's on the spreadsheet to a C. ex.
the word submit was changed to "cubmit" and any formulas with a S cell
location changed to a C cell location.

any thoughts?

thank you in advance! jane

My bet is that you're running xl2002+.

You selected your range and did:
Edit|Replace (or even Find)
Then you clicked on Find All

That showed you all the finds in that dialog--but it also changed your selection
(that initial column/range is no longer selected).

Then instead of reselecting your range, you hit Change All and kablewie
(ka-bloo-y!).

So avoid that intermediate FindAll or reselect the original range.


Jane wrote:

I highlighted a column that contained a simple formula in a very large
spreadsheet. I used REPLACE function to replace the cell locations in the
formula so that I didn't have to re-type the formula with the new cell
locations.

I wanted to change the formula from =SUM(S6:S20). I wanted to change the
formula to =SUM(C6:C20). I used REPLACE to do this before.

THIS time, the REPLACE function changed cells - formulas and text - outside
of my highlight resulting in ALL of the S's on the spreadsheet to a C. ex.
the word submit was changed to "cubmit" and any formulas with a S cell
location changed to a C cell location.

any thoughts?

thank you in advance! jane

--

Dave Peterson