Automation to Increment Number in Column

I have a column in a sheet [# Renewals], text/number that is initially set to 0 via an automated workflow when the new hire is added to the sheet. The new hire is issued a contract, which is approved or not approved.

When an approved contract is renewed (and it can renewed multiple times) via an approval workflow (every 365 days), I'd like have an automation that adds 1 to [# Renewals] so we know how many renewals have been approved over time.

When a contract is up for renewal, it sends approval request to manager, and changes [Renewal Approval (Status)] to "In Process", then it changes it to "Renew" or "Not Renew". This repeats each time the contract is renewed.

I have learned that you can't use formulas in the Chance Cell Value automation (unfortunately). I could use a helper column that takes [# Renewals] + 1 but not sure how to set up the formula to update this column since all of the approvals use the same column [Renewal Approval (Status)]. I was thinking something like whenever [Renewal Approval (Status)] changes to "Renew" somehow add 1 to [# Renewals]/

So, looking for ideas on how to keep a count of the number of renewals that were accepted. If "Not Renew" if ever selected, I want [# Renewals] to still show the number of renewals over time.

Find more posts tagged with

Accepted answers

All comments

Andrée Starå

https://community.smartsheet.com/discussion/144552/automation-to-increment-number-in-column

Hi,

Happy New Year! 🎉 🎆 🎇

I hope you're well and safe!

You could use a Workflow that adds 1 to a helper column and then counts it.

Does that make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD

✅ Did I help answer your question/solve the problem? Please support with 💡 ⬆️ ❤️ and/or ✅ Answer. This will make it easier for others to find a solution or help answer. I appreciate it, thank you! 🙏

Paul Newcome

If you insert a unique id on every row, you can use a copy row automation whenever the status changes then a formula with cross sheet references to count how many times that unique id is found int he second sheet containing the copied rows.

aschneiderheinze1025

This would be great @Andrée Starå , but that's what I was doing with the [# Renewals] column. I had a change cell value workflow that ran when the status of the renewal changed to "Renewed". I put "=[# Renewals]@row + 1", and it just put that formula in the cell.

How else could a workflow add 1 to a helper column? Would this add 1 each time a renewal occurred? I am just not getting how to have the Helper column or my [# Renewals] column increase by 1 each time a renewal occurs.

Paul Newcome

@aschneiderheinze1025 There is no way to use an automation to get a working formula in a sheet.

If you have a unique id on every row, you can use a copy row automation triggered when the status changes sending a copy of the rows to another sheet. Then you can use a COUNTIFS with cross sheet references to count how many times that unique id appears in the second sheet.

If you have access to the API, the premium add-on Bridge, or other similar third party apps, you could create something that triggers when your status changes, gets the current value, adds 1, then outputs the next number in an update row type of workflow.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.