I don’t know how often this occurs, but if it happens even once with a large number of cells it can be very annoying so I thought I would share this tip I created
I was recently asked about CANCELing the “Update Values” dialog during a Find and Replace All with many selections. This can be problematic when you have selected many cells, the replacement string is mistyped, and many of formulas will result in references that are not in the workbook. Other than shutting the computer off or ctrl-alt-delete to reboot there does not seem any way to cancel, excel requires cancellinig each and every one of the formula errors which can get annoying very rapidly.
For example:
If you have formulas of the type:
=John!A1
Running down a column for 1000 cells. If you want to change the John to Paul (to refer to data on another existing sheet), you can do a find (for “John”) and Replace (with “Paul”), select [Replace All] and there will be no problem.
If, however, you mistype and enter “Pauk” instead of “Paul”, the first replacement (=Pauk!A1) will not be a valid cell reference (presuming Pauk is not a worksheet name in the workbook). Excel will presume you want it to be an external reference and will pull up an “Update Values” dialog box asking for the file you mean. Since you now realize your mistake, you will [Cancel] and excel will go to the next cell and again will lead to the “Update Values” dialog in the second cell. This will occur for every invalid formula. Without shutting down or rebooting, there does not seem to a “Cancel All” available.
To avoid shutting down (in case you hadn’t saved) and avoid carpal tunnel symptons what you can do is to create a file that excel will link to to make all all the formulas valid:
Open a new instance of Excel (for example):
Start – Programs – Mircrosoft Office – Microsoft Excel (or equivalent)
Change the sheet name in that newly opened version to match the sheet name that results from Find/Replace (in this example “Pauk”)
SAVE AS the workbook to the default directory (the one opening in the “Update values” dialog) with the name of the sheet name being created (in this example “Pauk.xls”)
Once the file is saved, close the file (this instance of EXCEL may also be closed)
Go back to original instance of Excel (the one with the “update values” dialog), select this newly created file (“Pauk.xls”), [OK] and a link will be created to this file and now the rest of the formulas should just be changed without prompting since it is becoming a name that EXCEL understands and can find [The dialog comes since the formula reference is trying to link to a different workbook that it can not find and is asking for the user to provide that info]
Once all the changes have been completed, you can UNDO the changes and that should get you back to before the find/replace and you can then try again being more careful with the Find/Replace.
Steve
