REPLACE ALL issue in the Find and Replace fucntion in EXCEL 2016

[Frederick O'Brien]

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?

[#2]

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.

[#3]

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.

[#4 · Accepted solution]

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.

[#5]

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

[#6]

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