Formula en masse editing workaround

jb@59069 ✭✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

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.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!