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....

Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Answer ✓

    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.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/20/24

    @CarlaB

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

    If you using a Flag then

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

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • CarlaB
    CarlaB ✭✭✭✭
    edited 05/20/24

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

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/20/24

    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.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Answer ✓

    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.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • CarlaB
    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
    Mark.poole ✭✭✭✭✭✭

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!