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.

Andrée Starå

Hi @userpoets

I hope you're well and safe!

Unfortunately, it's not possible now if you don't have Control Center (and the sheet is provisioned through it), but it's an excellent idea!

Please submit this as a Product Feedback or Idea (If it hasn't been added already) when you have a moment.

Here's a possible workaround or workarounds

• You could have the value either in a so-called helper column or in the Sheet Summary and reference it in the formula.

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

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.