Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-02-2020, 02:23 PM
John Moses John Moses is offline Find/Replace a cell with external reference Windows 7 64bit Find/Replace a cell with external reference Office 2010
Novice
Find/Replace a cell with external reference
 
Join Date: Jan 2020
Posts: 1
John Moses is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 01-03-2020, 05:52 AM
NBVC's Avatar
NBVC NBVC is offline Find/Replace a cell with external reference Windows 10 Find/Replace a cell with external reference Office 2016
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

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.
Reply With Quote
Reply

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
Find/Replace a cell with external reference 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

Other Forums: Access Forums

All times are GMT -7. The time now is 10:46 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft