Formula en masse editing workaround
I'm hoping this may help other Smartsheet users that have run into the problem of not being able to edit formulas en masse within sheets.
I have hundreds of formulas per column (date ranged), multiple columns per sheet, multiple sheets per client. An example here:
=SUMIFS([Time Worked]:[Time Worked], [Date Worked]:[Date Worked], @cell > DATE(2018, 12, 15), [Date Worked]:[Date Worked], @cell < DATE(2019, 1, 1), [Deliverable/Project]:[Deliverable/Project], ="Clinical (e.g., case studies, trials, white paper)", Service:Service, ="Accounting", [Client Billable?]:[Client Billable?], 1)
Each formula Deliverable/Project has multiple Service values. There are multiple Deliverable/Project values.
The portion of each formula that needs changed is: (2018, 12, 15) and (2019, 1, 1).
What I had been doing was to edit the date values in: DATE(2018, 12, 15), [Date Worked]:[Date Worked], @cell < DATE(2019, 1, 1), highlight this segment, then Ctrl+C, Enter to save & close the formula, double click the next formula, highlight the segment, Ctrl+V, Enter, double click the next formula, etc., etc.
Exporting the sheet to Excel does not bring the formulas across. Ctrl+C the formula into Excel gets errors due to syntax. Once you can get a Smartsheet formula into Excel, Find & Replace does not work, again due to syntax.
Ok, so my formulas in Smartsheet are in Text columns and Word is a glorified text editor. So I copied a formula into Word and ran Find & Replace. It worked.
So I created a Word doc for each client and each type of formula family I have, copied a category of formulas into a two column, muti-row table in Word. I then labeled the table to describe the Deliverable/Project and Service.
I then ran Find & Replace to edit the DATE(2018, 12, 15), [Date Worked]:[Date Worked], @cell < DATE(2019, 1, 1) fields. Every field got updated.
I could then copy this table, run Find & Replace to update the Deliverable/Project to the next value, copy that table, update Deliverable/Project, repeat, repeat, repeat as necessary.
Now I can open the appropriate Word doc, run Find & Replace to update the DATE(2018, 12, 15), [Date Worked]:[Date Worked], @cell < DATE(2019, 1, 1), save, then Copy & Paste into the new column in Smartsheet and my formulas are updated as necessary.
Still having to go outside Smartsheet rather than having a formula editor within Smartsheet, but it is MUCH, MUCH better than having to modify every formula individually.
Attached is a snip of part of one Word doc I can now update in mere seconds, this one has 108 formulas for a single Smartsheet column. Each set of 9 formulas can be copied and pasted into the appropriate Smartsheet location.
Hopefully this will help someone else.
Comments
-
Hi Jerry,
Nicely done!
Thanks for sharing!
Have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
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.
-
THANK YOU!!!!!!! This work around has saved me hours!!!!
-
You are a true hero!!!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 283 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!