Find/Replace a cell with external reference
I am using excel 2010, and I have a master sheet that collects data from multiple files. The master sheet has hundreds of references that look like this:
='C:\Users\John\Dropbox\JXLE\Mgmt Reports\Chris Court-2312\[2019 Chris Court PM Current Statement.xlsx]Sheet1'!O27
Note that the name of the referenced file starts with the year 2019. I need to change the reference to 2019 to 2020. Hundreds of cells need this change. To do so, I select "Formulas -> Show Formulas". This presents all of the hundreds of external references. Then I select "Home-> Find&Select->Replace and replace 2019 with 2020.
Sadly, this results in unexpected behavior in that instead of making the change on my master sheet, it opens a dialogue box requesting a new file, as though I wanted to open or save to a different file.
What command sequence would I need to invoke in order to change name of the file in a cell that contains an external reference.
Thanks for any insight you might offer.
John
|