BREAK A FIND/REPLACE OPERATION

[Faraz A. Qureshi]

I often get in the problem of having a wrong filename/range/reference
inserted while updating a formula by way of FIND/REPLACE. The cells, if in
quite a large number, do cause inconvenience upon popup of UPDATE VALUES.

Any idea how to break such an operation?

Thanx in advance!

 

[Jacob Skaria]

Faraz

How about using the 'Formula Auditing' mode for find/replace.

To activate/deactivate use keys Ctrl and ~

If this post helps click Yes

 

[Faraz A. Qureshi]

How can Formula Auditing help. Let me explain the issue more via an example:

I have five files opened:
a) Comparison;
b) File1;
c) File2;
d) File3;
e) File4;

I am using 669 cells of each of the four columns of Comparison.xlsx by
inserting a vlookup formula so as to get the relevant figures from
File1.xlsx, File2.xlsx, File3.xlsx and File4.xlsx.

Instead of inserting entering formulas for all the four columns, I inserted
the same for the first column and copied the same for the rest of the three.

However, I selected the second column later and replaced the string "File1"
with "File2" so as to have the same working for File2. But while changing the
formula for Column3 instead of replacing "File1" with "File3" I accidently
entered "File03" in the Replace With.

Now, Excel has the popup of UPDATE VALUES so as to have the File03 be found
and opened for each and every of 669 cells.

 

[Faraz A. Qureshi]

In tghis case unfortunately the only way out I find is to open the Task
Manager and close EXCEL and loose work on all other opened files as well.

 

[Jacob Skaria]

From your post..

Instead of inserting entering formulas for all the four columns, I inserted
the same for the first column and copied the same for the rest of the three.

I would prefer

--Build the formula in the 1st column (1st row)
--Copy the formula to the next 3 columns (again only in the 1st row)
--Edit the formulas in the 3 columns (only in the 1st row)
--Select the 4 cells of the first row; and then drag down the selection as
required..


If this post helps click Yes

 

[Faraz A. Qureshi]

Good approach. This is usually what I do, but IF troubled in such a condition
I think there is no other way.

Thanx anyway, pal!