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?
|