Editing numerous linked cells at once to future workbook

[AlinaH]

Hello, I am working on a template that will link to workbooks that are sent to my team on a quarterly basis. The workbook we receive is saved in a standard format with standard naming configuration of both the workbook and folder in which is it stored within a shared drive (the only difference is the naming of the quarter/year). I have tediously linked previous workbooks and want to create links for future workbooks (i.e. for 2025, 2026 etc..). In the past (many years ago) I was able to go to "show formulas" and use ctrl+f to "replace all" of a selection of cells to update the year (i.e. 4 to 5, 5 to 6, etc..). Now, when I try to do this excel is making me select an existing workbook before making the next replacement of a single digit within a single link, one by one. I have to press cancel so it wont link to the wrong workbook and then go to the next cell to replace the linked formula and go through the whole tedious process of canceling out of selecting a workbook. How can I use replace all, or some other way, to create links to future workbooks without having to go cell by cell?

Thank you in advance.

[Bernie Deitrick]

This may work - select all your cells with links and run the first macro below. That will convert all your formulas to strings. Then copy the cells and paste them where you want the new formulas. Do your find and replace and the formula strings will be modified to reflect your expected file name - but keep them as strings.

Then return to the original range of formulas and run the second macro. That will remake the strings in thosee cells back into formulas.

Then, when you have the 'new file' available, run the second macro on your modified strings, and the links should work.

Please Login or Register  to view this content.