How to set a value in one column based on value of another column

I have 3 columns:

"auto complete fill", "status", and "% complete"

I set % complete manually, but would like to automatically set it to 100% when status = Complete.

Since the formula get's overwritten when I manually set % complete, I created the helper column "auto complete fill" to hold a formula, hoping to use the formula to set the value of % complete if status = complete.

This is the formula I attempted to use in the auto complete cell: =if (status@row = "complete", [% complete]@row =100, "")

I get an error invalid column value. Seems SS won't let me set a new value in a different cell. The formula always assumes you're referencing the current cell. Is there a way to do this?

Find more posts tagged with

Accepted answers

Kurt Robohm

Perfecto! Thank you. Works great!

All comments

Nick Korna

Hi @Kurt Robohm,

You can do this a bit more easily with automation and without needing the auto-complete fill column addition:

As you're entering the %age complete manually then a formula would be overwritten - if you had several status stages with defined %ages attached then you could use a nested IF statement instead to update the percentage instead.

Hope this helps; if you've any questions then just ask!

Kurt Robohm

Perfecto! Thank you. Works great!

lclarkNE

I attempted to use this automation to update a column with a check box to Yes but it checked all the rows even though it did not contain the criteria.

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.