Forum Discussion
REPLACE ALL issue in the Find and Replace fucntion in EXCEL 2016
Ok. I am still not sure if I understood.
Well, lets try the followings.
A) can you change your Calculation method from AUtomatic to manual and then do the find and replace and see if it still asks to confirm? Please remember to change the method back to automatic after the replacement of links.
B) Please go to File> Options> Advanced, please see whether the tickmark shown in screenshot below is marked or unmarked.
Please post back the result.
Jamil,
I checked both of your suggestions and changing from automatic to manual made no difference and the automatic link to other workbooks was checked properly.
I read the help section where it suggests that asterisks can be used to replace portions of formulas and lo and behold that made it process the 154 cells in this sample spreadsheet but unfortunately it did a literal replacement including the asterisks which totally ruins the spreadsheet. I have attached a Word snapshot of the before and after the Search and Replace so see if you can spot a problem with what I entered.
This is the exact count of cells that were successfully updated in Excel 2007 up until when I switched over to Excel 2016 in January.
Thanks..
Jamil,
Whew! I found the problem(s). If you noticed in the Find & Replace strings there was a one little thing I missed; there were 4 numbers "1712" (year 17, month 12) which I missed changing to "0118" (year 18, month 1). Once I corrected that, all 154 cells changed instantly. Voila. I either need new glasses or a whole lot more concentration..!! The second issue was that once the master document realized that it was in the same folder as the lower level document, Excel automatically dropped the Directory Folder from the address of the lower level folder. So I was looking at the newly copied files into the new 2018 folder and I was typing in part of the folder name as part of the search string. But the converted 12/2017 master file was not showing the complete folder path and therefore there was no match because I had put a few characters in the beginning of the string to capture the "18" part of the folder name.
But thanks for your efforts because that was what got me truly concentrating today....!
So now the upgrade each month will take just seconds as it always has...
Thanks again for ideas and suggestions..!!!!
Fred