#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
The new files need to exist for this to work. If the files don't exist, Excel will open the dialogue box to help you locate an existing file. if the files exist, then the find/replace works as expected.
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Find/Replace in a selected cell(s), but across entire workbook | ue418 | Excel | 1 | 12-21-2017 12:32 AM |
Find/Replace using format of cell | catflap | Excel | 1 | 09-11-2017 07:28 AM |
Select Cell Text to paste into Find/Replace | CBarry | Word VBA | 2 | 02-16-2017 04:37 AM |
Using Cell Values in Active Sheet to Populate Path to External Reference Link | wowcrofty | Excel | 4 | 12-19-2016 12:29 PM |
Reference to an external filename which changes frequently | roooberta | Word | 0 | 12-11-2008 07:33 AM |