lost_in_the_sauce
Board Regular
- Joined
- Jan 18, 2021
- Messages
- 128
- Office Version
- 365
- Platform
- 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?
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?