Find and Replace in Formulas

Joey Tomacchio

Hello,

I use a TON of formulas that involve employee names. When creating sheets for new employees I need to paste the names into each formula. It takes FOREVER because I cannot find and replace within the formulas. Am I missing something?

Also, how do I stop this from happening? I can't make a column "number only" so I get lots of extraneous entries that skew my data.

Thank you!

Andrée Starå

Hi @Joey Tomacchio

I hope you're well and safe!

Unfortunately, it's not possible to find/replace in a formula without the Premium App, Control Center, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment.

Here's a possible workaround or workarounds

• You could add a cell/column or use the Sheet Summary, change the value there, and reference it in the formulas.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

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 or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Joey Tomacchio

I'm not exactly sure what you mean. The formula is already referencing a column, which is a drop down of employee names. Let me show you, it'll be easier :-)

=SUMIFS(Errors:Errors, ErrorType:ErrorType, ="STAPLES NOT REMOVED", Name:Name, ="John Doe", Month:Month, ="01")

It's meant to count the number of this specific type of errors for this employee for this specific month.

I've used the summary sheet to total my errors for each month across the company but I don't see how it would work for the individuals and the individual errors for each month.

Thanks!

Andrée Starå

@Joey Tomacchio

Happy to help!

Try something like this.

Add so-called helper Sheet Summary Fields and name it Name and Month (you can do the same for the ErrorType)

Update your formula like below.

=SUMIFS(Errors:Errors, ErrorType:ErrorType, ="STAPLES NOT REMOVED", 
Name:Name, Name#, Month:Month, Month#)

Make sense?

Would that work/help?

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

Joey Tomacchio

I’m sorry…. I’m not sure what you mean.

Andrée Starå

@Joey Tomacchio

Here's more information.

https://help.smartsheet.com/articles/2476786-define-your-work-with-sheet-summary?source=apphelpicon

Make sense?

Deonizia Egan

If you haven't worked this out yet, I think you would be better served by creating a Metrics sheet and setting up your formulas there, and then you can do what Andree said and create reference rows for your formulas.