Change formula reference in bulk?

D

diaare

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
 
T

Tyro

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
 
J

JLatham

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.
 
J

J. Sperry

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.
 
D

diaare

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
 
Top