Trying to copy rows & then mark them 'done'

I'm trying to do something that seems simple but is really frustrating me. I want to copy certain rows to another sheet and mark them 'done' afterward.

I've got an automation that copies rows over to another sheet, then records a date & locks the row. I also want to have it change a value in another cell (mark it 'Complete'), but it won't let me add the action without deleting one of the others. I've tried adding the 3 actions in different orders -- I cannot get all 3 to exist.

I tried making a second automation to run when a row changed and if the 'Sent to other sheet' column had a date, then mark the 'Status' cell complete -- but that doesn't work because I can pick a specific date (but not 'today') or blank (but I want NOT blank) or 'any value' (but that would include blanks and nonblanks). I can't pick 'only those that aren't blank'.

So I tried making a formula to update the 'Status' column, but that also failed.

Formula in dropdown list column [Status_Task]. I want it to look at 'SentToTranscriptsSS' to see there's a date in that cell. If there is a date, mark the Status column 'Complete'. If there's no date (is blank), then don't change the value in Status. I don't want to change 'Status' to a particular value if there's no date -- I want to leave it unchanged with whatever value.

I'm not sure how to write the "leave it unchanged" bit -- leave off the 'is false' expression? Tell it to use the existing 'Status' value if false?

This is what I've got, but results are #unparseable regardless if Sent has a date or not.
=IF(AND(CONTAINS("ranscript", [Rounds task]@row), (NOT(ISBLANK([SentToTranscriptSS])@row))), 'Complete', [Status_Task]@row)

I also tried this with #unparsable error as well.
=IF(AND(CONTAINS("ranscript", [Rounds task]@row), (NOT(ISBLANK([SentToTranscriptSS])@row))), 'Complete')

This really shouldn't be this hard....

Find more posts tagged with

Formulas

Automations

Accepted answers

Mark.poole

Ok which ever column gets the date when it’s been copied to another sheet. I’ll call it date

=if(date@row <>””,”Complete”)

put this formula in the column you want saying complete.

All comments

Mark.poole

@CarlaB

=IF([Sent to other sheet]@row<>"","Complete",[Status_Task]@row)

If you using a Flag then

=IF([Sent to other sheet]@row<>"",1)

CarlaB

@mark.poole Bummer…. That looked much more elegant & simple, but it gives a #circular reference error. :-(

Mark.poole

ok. So if there is a circular ref. That means you have a formula pointing at something the other formula is looking at. So I’m assuming it’s because status task is where the complete will be.

Mark.poole

Ok which ever column gets the date when it’s been copied to another sheet. I’ll call it date

=if(date@row <>””,”Complete”)

put this formula in the column you want saying complete.

CarlaB

THANK YOU, @Mark.poole! I knew it had to be simple, but I was completely brain glitching on it. That worked a treat.

Mark.poole

https://community.smartsheet.com/discussion/comment/437054#Comment_437054

Absolutely. I'm glad it worked out for you.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.