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
-
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
-
=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.
-
@mark.poole Bummer…. That looked much more elegant & simple, but it gives a #circular reference error. :-(
-
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.
-
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.
-
THANK YOU, @Mark.poole! I knew it had to be simple, but I was completely brain glitching on it. That worked a treat.
-
Absolutely. I'm glad it worked out for you.
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 458 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 137 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!