Change formula reference in bulk?
 

I need to change thousands of formulas on a worksheet. Afeter reading some
other posts in this forum, I thought I could do it using find and replace.

Here is what I have tried to so far:

I converted all the formulas to text by using the find = and replace with
$$$=. This was very fast.

I changed my workbook reference from jan-june to july-dec using the find and
replace. Again this was very fast.

Here is my hangup...

When I attempt to change the $$$= back to = using find and replace it slows
excel to a halt, and I end up having to use task manager to get out of it.

This even happens when I have manual calculations turned off.

Is there a step I am missing to complete this process, or am I going about
this all wrong?

Thanks,

Diane

Change formula reference in bulk?
 

With thousands of formulas,. Excel may be recomputing many, many thousands
of times. As each $$$= becomes =, Excel may be computing the formula. This
could be very, very time consuming. The question is: what are you trying to
do? You did not tell us.

Tyro

"diaare" wrote in message
...
I need to change thousands of formulas on a worksheet. Afeter reading some
other posts in this forum, I thought I could do it using find and replace.

Here is what I have tried to so far:

I converted all the formulas to text by using the find = and replace with
$$$=. This was very fast.

I changed my workbook reference from jan-june to july-dec using the find
and
replace. Again this was very fast.

Here is my hangup...

When I attempt to change the $$$= back to = using find and replace it
slows
excel to a halt, and I end up having to use task manager to get out of it.

This even happens when I have manual calculations turned off.

Is there a step I am missing to complete this process, or am I going about
this all wrong?

Thanks,

Diane

Change formula reference in bulk?
 

I would have approached it differently.

I would have first set calculation to manual and then used find and replace
for the jan-june to july-dec with the "Look in Formulas" option.

Then I would have set calculation back to automatic and perhaps even pressed
[F9] at that point and headed off to get a fresh cup of coffee, because
somewhere in all of this the recalculations are probably going to take a
while.


"diaare" wrote:

I need to change thousands of formulas on a worksheet. Afeter reading some
other posts in this forum, I thought I could do it using find and replace.

Here is what I have tried to so far:

I converted all the formulas to text by using the find = and replace with
$$$=. This was very fast.

I changed my workbook reference from jan-june to july-dec using the find and
replace. Again this was very fast.

Here is my hangup...

When I attempt to change the $$$= back to = using find and replace it slows
excel to a halt, and I end up having to use task manager to get out of it.

This even happens when I have manual calculations turned off.

Is there a step I am missing to complete this process, or am I going about
this all wrong?

Thanks,

Diane

Change formula reference in bulk?
 

For the last step, instead of performing the find/replace on the entire
worksheet, break it up into 10 or so chunks. Select 1/10th of the cells, and
perform the find/replace on that selection. Repeat on the next 1/10th, etc.

Having manual calculations turned on (what I think you meant) won't help,
because Excel will have to calculate each cell as soon as it becomes a
formula.

"diaare" wrote:

I need to change thousands of formulas on a worksheet. Afeter reading some
other posts in this forum, I thought I could do it using find and replace.

Here is what I have tried to so far:

I converted all the formulas to text by using the find = and replace with
$$$=. This was very fast.

I changed my workbook reference from jan-june to july-dec using the find and
replace. Again this was very fast.

Here is my hangup...

When I attempt to change the $$$= back to = using find and replace it slows
excel to a halt, and I end up having to use task manager to get out of it.

This even happens when I have manual calculations turned off.

Is there a step I am missing to complete this process, or am I going about
this all wrong?

Thanks,

Diane

Change formula reference in bulk?
 

Thank you all for your help. After a little more trial and error I was able
to get it to work. It still took a few minutes, but did finally complete
without crashing excel.

Thanks very much for the "in formulas" tip...I knew it was there but had
forgotten about it.

Thanks Again,
Diane

"J. Sperry" wrote:

For the last step, instead of performing the find/replace on the entire
worksheet, break it up into 10 or so chunks. Select 1/10th of the cells, and
perform the find/replace on that selection. Repeat on the next 1/10th, etc.

Having manual calculations turned on (what I think you meant) won't help,
because Excel will have to calculate each cell as soon as it becomes a
formula.

"diaare" wrote:

I need to change thousands of formulas on a worksheet. Afeter reading some
other posts in this forum, I thought I could do it using find and replace.

Here is what I have tried to so far:

I converted all the formulas to text by using the find = and replace with
$$$=. This was very fast.

I changed my workbook reference from jan-june to july-dec using the find and
replace. Again this was very fast.

Here is my hangup...

When I attempt to change the $$$= back to = using find and replace it slows
excel to a halt, and I end up having to use task manager to get out of it.

This even happens when I have manual calculations turned off.

Is there a step I am missing to complete this process, or am I going about
this all wrong?

Thanks,

Diane