Replacing values in formulae with links
 

I need help with replacing values in formulae with links.

I inherited supporting several Excel workbooks with very involved formulae
involving links

For example:
=SUM(IF('\\ttemail\global\Operations\Forecasting\I nbound\[Inbound CC Stats
Call
Volumes.xls]C-Rcvd'!$F$4:$F$978=$B1,'\\ttemail\global\Operations \Forecasting\Inbound\[Inbound
CC Stats Call
Volumes.xls]C-Rcvd'!O$4:P$978,))+SUM(IF('\\ttemail\global\Operat ions\Forecasting\Inbound\[Inbound
CC Stats Call Volumes
Tucson.xls]C-Rcvd'!$F$4:$F$978=$B1,'\\ttemail\global\Operations \Forecasting\Inbound\[Inbound CC Stats Call Volumes Tucson.xls]C-Rcvd'!O$4:P$978))

and what I need to do is a global replace of all the instances of $978 with
$1343

The problem is that when I try to use Edit|Replace (Look in Formulas) the
process seems to get hung.

I assume that each time Excel changes the formula it then tries to update
the cells, and that it would therefore take much longer than necessary.

I even tried using C# and the Excel COM objects to do this programmatically,
but the same problem happened.

How can I accomplish this?
Is there a way to turn off updates as the replacements are being made?

Replacing values in formulae with links
 

Have you tried setting calculation to manual (Tools/Options/Calculation)?

Or, you could try converting the links to text first by Edit/Replace
Find =
Replace With %%

Then Edit/Replace the cell references you want and convert the text back to
formulas with
Find %%
Replace With =

Just be sure you back up your work and don't move anything around if you
change the formulas to text, otherwise when you change them back the your
formulas will have incorrect references in them.


"PIEBALD" wrote:

I need help with replacing values in formulae with links.

I inherited supporting several Excel workbooks with very involved formulae
involving links

For example:
=SUM(IF('\\ttemail\global\Operations\Forecasting\I nbound\[Inbound CC Stats
Call
Volumes.xls]C-Rcvd'!$F$4:$F$978=$B1,'\\ttemail\global\Operations \Forecasting\Inbound\[Inbound
CC Stats Call
Volumes.xls]C-Rcvd'!O$4:P$978,))+SUM(IF('\\ttemail\global\Operat ions\Forecasting\Inbound\[Inbound
CC Stats Call Volumes
Tucson.xls]C-Rcvd'!$F$4:$F$978=$B1,'\\ttemail\global\Operations \Forecasting\Inbound\[Inbound CC Stats Call Volumes Tucson.xls]C-Rcvd'!O$4:P$978))

and what I need to do is a global replace of all the instances of $978 with
$1343

The problem is that when I try to use Edit|Replace (Look in Formulas) the
process seems to get hung.

I assume that each time Excel changes the formula it then tries to update
the cells, and that it would therefore take much longer than necessary.

I even tried using C# and the Excel COM objects to do this programmatically,
but the same problem happened.

How can I accomplish this?
Is there a way to turn off updates as the replacements are being made?

Replacing values in formulae with links
 

Thanks,

Have you tried setting calculation to manual (Tools/Options/Calculation)?

That seems to help, but it still takes a long time.


Or, you could try converting the links to text first by Edit/Replace
Find =
Replace With %%

Then Edit/Replace the cell references you want and convert the text back to
formulas with
Find %%
Replace With =

I may yet try that.

Replacing values in formulae with links
 

Or, you could try converting the links to text first by Edit/Replace
Find =
Replace With %%

Then Edit/Replace the cell references you want and convert the text back to
formulas with
Find %%
Replace With =

That didn't really help, but now I know that just _one_ of these sheets has
1600+ formulae and 6000+ occurences of $978

However, the problem still occurs when I do the last step.