I have a workbook, call it "Year 8" that has hundreds of references to data from a named range on a separate workbook, call it "New Year 8 Master".
I want to use "Year 8" as a template for four other similar workbooks, called "Year 9", "Year 10", etc. Each of these would reference separate workbooks, ie "New Year 9 Master", "New Year 10 Master", etc.
I've opened "New Year 9 Master" and saved "Year 8" as "Year 9". I've then tried a find and replace process so that every formula that refers to "New Year 8 Master" now refers to "New Year 9 Master" but as each change occurs, a dialogue box opens asking to identify the linked workbook. With so many links, the process will take hours.
I don't suppose anyone can identify a workaround? I was thinking of using some VBA to find and replace each Year 8 reference and change it to Year 9... will that work?
An example of a formula (forgive any crassness) is shown below.
=IFERROR(INDEX('New Year 8 Master.xlsx'!MainDataTable,MATCH(H5,'New Year 8 Master.xlsx'!Forename_Surname,0)-1,MATCH($AF$5,'New Year 8 Master.xlsx'!Titles,0)),"")
Many thanks in advance for any suggestions.