Find and replace inside a formula
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!
Best Answer
-
@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.
Answers
-
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!
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!