Problem with nested IF statements

I was successfully able to write a formula to fill in the value of the User 2 Action column with the value of User 1 Action if Prod User 2 is not blank:

=IF(AND(NOT(ISBLANK([Prod User 2]@row)), NOT(ISBLANK([User 1 Action]@row))), [User 1 Action]@row), "")

I would like to add one more level to this where the above works only if the value of the Update Flag column is equal to "Yes". I've looked at other nested questions and tried many different iterations but can't seem to figure it out. I'm not even sure if what I want to do is possible but any help would be greatly appreciated.

In the screenshot, the 2nd row should execute my formula above if Update Flag = "Yes" but should leave the 3rd row alone because Update Flag is blank. My formula, as is, will always change the value of User 2 Action if Prod User 2 is not blank.

Find more posts tagged with

Formulas

Accepted answers

All comments

Carson Penticuff

I'm not sure I am understanding your case correctly, but it sounds like you can just add another condition to your AND statement instead of nesting an extra IF.

=IF(AND(NOT(ISBLANK([Prod User 2]@row)), NOT(ISBLANK([User 1 Action]@row)), [Update Flag]@row = "Yes"), [User 1 Action]@row, "")

I am a little confused with "will always change the value of User 2 Action". Are you saying that [User 2 Action] will be filled in manually, but you want it to be changed to a different value under the conditions specified above?

Scott_Davis

I may not have stated my case well (and apologies for any confusion) but the code you supplied is exactly what I was after. Thanks for the help!

We were hoping that we'd be able to apply the formula to an existing sheet. In short, the form in front of the sheets currently allows users to request Add and Remove on the same row (this causes issues with an automation driven by the Smartsheet). We are modifying the form and using the formula to keep things cleaner going forward so one row only has Add or Remove not both. I didn't want to change existing values of User 2 Action with the formula so the Update Flag is intended to only apply the formula to the new rows.

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.