Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing formulae links | Excel Programming | |||
Macro replacing links | Excel Discussion (Misc queries) | |||
Replacing Formulae With Values.....Continuation | Excel Programming | |||
Another Question Concerning Replacing Formulae With Values ! | Excel Programming | |||
Replacing links with values | Excel Programming |