Capture Snapshot of Linked Cell at a point in time

Alexis Kipping

I would like to capture the value of my Current Launch Timing (QC Release) when projects are submitted for Gate 4 automatically. Both columns are Date columns. Current Launch Timing (QC Release) is currently linked to the project plan. I don't want the value to continually update as the timeline updates. There doesn't appear to be a workflow that will capture the value for me (if there is, please let me know!) I have this formula that will capture the date when triggered, but once triggered, it will continually updated. I'm open to any suggestions. Would converting to text somehow work?

=IF(ISDATE([Gate 4 Ready to Route Date]@row), [Current Launch Timing (QC Release)]@row, "")

Paul Newcome

Are you able to provide some screenshots for context?

Andrée Starå

Hi @Alexis Kipping

I hope you're well and safe!

You could copy the row to another sheet as a log.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Alexis Kipping

@Paul Newcome here is a screen shot, there's not much to go with, but i would replicate the solution if found at Gates 3 and Gates 5. I only have 1 Current Launch Timing (QC Release) column.

@Andrée Starå i guess that would work, but then i would need to pull that value back into my main table with . . . would love to avoid a separate table if possible. There will be multiple gates and there many projects.

Andrée Starå

@Alexis Kipping

If you want to explore getting the value back to the sheet.

Please have a look at my post below with a method I developed.

More info: 

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work/help?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

The only way to capture static data is to have the data copied to another sheet and then use a formula with cross sheet references to pull it back over. What kind of scale are we looking at? How many gates and how many projects?

We can leverage the method I developed for capturing a date/time stamp to capture the static data once it has been copied.

https://community.smartsheet.com/discussion/63561/date-time-stamp-option

Alexis Kipping

@Paul Newcome and @Andrée Starå I have 3 gates that I would want to have this captured, and an active project portfolio at anytime of at least 100 projects at the various stages.

I will explore both of these options. I have a unique identifier that should make it fairly straight forward to bring the data back in.

Thank you both for your help!

Andrée Starå

@Alexis Kipping

Excellent!

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

You should be ok then for scaling. The method I posted above is specifically for capturing a date/time stamp of when an action occurs. You can just set up the copy row automation to run when the gate date is filled out and then use an INDEX/COLLECT combo to pull in the proper date for that id/gate combo.

=INDEX(COLLECT({Date Column To Pull In}, {Gate Date Column}, @cell <> "", {ID Column}, @cell = [ID Column]@row), 1)