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,
Thanks for the link to those add-ins. Unfortunately, they aren't what I need to manage updating links from one workbook to another.
If I can simplify my problem; I maintain end-of-month reports of my investments and to simplify the data collection I create a folder for each month of the year and when I have completed one month, I copy the files as is, to the next months folder. Then I rename the worksheets in the new folder and go into the summary report and update the links to direct them to the sub-spreadsheet (target) that has now been renamed to the present month. There are numerous links from the summary report to the target report (65 or more) and with EXCEL 2007, when I do a FIND/REPLACE ALL, in a few microseconds all 65+ links get changed and linked to the newly renamed target spreadsheet. In EXCEL 2016, each link opens a window in which I have to select the target file (multiple clicks to navigate through Explorer) and then to another window where I click on OK. So now it would involve maybe 7-8 clicks for each link which means it would take 30 minutes or more to edit all the links vs. the few seconds as before in version 2007.
So this appears to be something that I need to re-configure for EXCEL 2016 to make it work like the 2007 version.
Again, thanks for your effort.
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