Find/Replace not working in other workbook reference

Status
Not open for further replies.

lost_in_the_sauce

New Member
Joined
Jan 18, 2021
Messages
34
Office Version
  1. 365
Platform
  1. Windows
I have a sheet that has multiple cells referencing multiple other workbooks for comparison of values. Normally I simply use a Find/Replace when I need to update a bunch of reference values to the same workbook (i.e. adding a new month), but trying to use Find/Replace within formulas that reference other workbooks doesn't likeit when I try to Find/Replace wither the path to the workbook or the name of the worknbook itself without opening a window for every cell one by one to click and confirm the new path.

Is there a way to not have to confirm every single one and just Find/Replace them all?

Example:

=SUMIFS('FY21'!$R:$R, 'FY21'!$I:$I, "ACTUALS", 'FY21'!$F:$F, MAIN!$B6)

This formula sums all values on my FY21 data tab in column R as ling as the meet the criteria of "ACTUALS" ion column I and match the category in cell B6 in column F.

If I FIND/REPLACE $R:$R with $S:$S, then it does the same thing but in the next column over for the next month values. Great for updating a lot of cells at once.

However, when I attempt to FIND/REPLACE a workbook reference, example:

='F:\BUDGET\Monthly\FY21\Feb 21\[FY21 FEB - Presentation.xlsx]Overhead'!$DR$8

And I want to change every cell to this:

='F:\BUDGET\Monthly P&L\FY21\Mar 21\[FY21 MAR - Presentation.xlsx]Overhead'!$DR$8

So I FIND/REPLACE

Feb 21\[FY21 FEB

with

Mar 21\[FY21 MAR

and for every single cell it opens a window for me to select the folder/file, which wouldn't be bad if I didn't need to do it a few hundred times. Way around that to just FIND/REPLACE without the window pop up?
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

lost_in_the_sauce

New Member
Joined
Jan 18, 2021
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Ugh, nevermind, it seems to be working. I think yesterday I was only trying to FIND/REPLACE within the reference i.e. "Feb 21" to Mar 21" and that was giving me problems, when I extended the string to include part of the file name it just did it.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
Duplicate to: Mass Find/Replace other worksheet references

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,129,383
Messages
5,635,942
Members
416,889
Latest member
dhegs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top