Formula en masse editing workaround

jb@59069

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.

Andrée Starå

Hi Jerry,

Nicely done!

Thanks for sharing!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

shawna kalke

THANK YOU!!!!!!! This work around has saved me hours!!!!

bgordon1

You are a true hero!!!!