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..
