Forum Discussion
REPLACE ALL issue in the Find and Replace fucntion in EXCEL 2016
On a monthly basis I need to change multiple links from one spreadsheet to another because I create a new version of the target spreadsheet (and the upper level spreadsheet) for each month of the year. This is to be able to save a monthly record of a large amount of financial information and keep the spreadsheets to a reasonable size. The upper level spreadsheet links back to specific cells in the target spreadsheet. Normally I use the Find and Replace function and I will copy/paste the link such as
"2017Invest\Detail 1217\[17-12TAXABLE.xls]Summary" into both the Find and Replace lines in the window that pops up. I will then edit the Replace line to reflect year 2018 and month 01 (January) and click on REPLACE ALL and I instantly get 65 cells replaced with the text above. There are specific cells in the original upper level links referring to the cells in the target spreadsheet and they show up in the upper level spreadsheet unaltered because only the specified text gets replaced (theoretically). However, today when I click on REPLACE ALL, another window shows up asking me to navigate the the folder and file name of the target folder and then another window asking for me to click on OK. To make all the replacements would take a total of more than 1000 clicks where last month it only took ONE click. Worse yet, most of the changes, although directed to the appropriate cell on the target file, do not actually read the cell data from the target file because some of the links trailing target cell data gets replaced with other text which creates an error, a REF in the upper level cell...
I just upgraded to EXCEL 2016 3 weeks ago so this the first time I've tried this updating procedure since I took EXCEL 2007 off my PC so is it possible that there is a setting someplace that I need to change to allow Replace to actually allow replacing all the links? Or is there a problem with Excel not knowing that I want to just replace the specific text that I am entering into the Find/Replace window?
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.
- My friend Bill Manville has two wonderful Add-Ins specifically made to manage links and these add-ins are for free available here http://www.manville.org.uk/software/index.htm Please take a look.
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.
