Find and replace inside a formula

userpoets

Good afternoon, I am working on a sheet to track various stats for my department. The data is being pulled and collated from a master sheet, so that the master sheet has all the raw data and my sheet has the data collated and organized.

This is my base formula:

=SUMIFS({ComplianceReports_Total}, {ComplianceReports_ClientName}, "ACME", {ComplianceReports_ReportType}, "Average", {ComplianceReports_Platform}, "Stats", {ComplianceReports_Year}, "2022")

It works well in and of itself, but I have 650 rows and 45 columns of data, all of which will be a variation on this base formula.

Is there a way for me to do a find/replace by row of the specified criteria? If I can build Row 1, copy to Row 2, and do a find/replace of Client Name from "ACME" to "Food Lion" for example, I would have this completed in far less time than it would be manually making each cell adjustment.

Thanks in advance!

Samuel Mueller

@userpoets this may not be your best solution, but if you put a ' in front of your formula like so

'=SUMIFS({ComplianceReports_Total}, {ComplianceReports_ClientName}, "ACME", {ComplianceReports_ReportType}, "Average", {ComplianceReports_Platform}, "Stats", {ComplianceReports_Year}, "2022")

and drag that across your rows you could do a find an replace at each one (highlight at least 2 cells and do the find replace). You still have to create all the different cross sheet references and all that though.

If you are really handy with an API you could use the API to loop through columns and create cross sheet references for you so you don't have to do that manually. However you have to spend the time to write the script so either way.