Lock or Store Date/Value Solution without using Zapier

Andrée Starå · 05/24/20

Hi all,

I hope you're well and safe!

I've developed a solution that lets youstore the date or value.

You'd use the copy-row automation and a VLOOKUP or combination of INDEX/MATCH to make it work.

We'd trigger the copy-row to another sheet and get the created date/other value and then use the VLOOKUP/INDEX/MATCH to get it back to the main sheet in another so-called helper column. As long as you have a unique id/value that we can use, it will work.

Also, this opens up more options with auto-numbering and similar.

Depending on other workflows, you could reuse one sheet for multiple workflows.

I hope that helps!

Let me know if you have any questions!

Stay safe and have a fantastic day!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please help the Community bymarking it as helpful. It will make it easier for others to find a solution.

jg124 · 06/21/20

Hey Andrée, could you elaborate further on how this would be set up? It’s exactly what I’m looking for.

Andrée Starå · 06/23/20

@jg124

I'd be happy to share an example.

Please send me an email at[email protected], and I'll share it with you.

Kumiko Percival · 08/01/20

Hi Andrée

Thank you for sharing your workaround for this. I would very much like to implement your solution, however, I do not currently have a "unique id/value" in the rows of interest (other than the row numbers themselves). Do you have any recommendations on generating a column of unique ids/values to be used?

The Task Name of the parent row combined with the Task Name of the child row could generate unique ids/values, however I am unsure of how to combine these into a helper column (noting that only individual child rows will be copied using the copy-row automation).

Please let me know if any of this is unclear, I am very new to SmartSheet and may not be using the appropriate terms to describe my issue!

Thank you!

Kumiko Percival · 08/01/20

没关系,我能够生成一列单que "Row IDs" using =COUNTIFS([Task Name]$1:[Task Name]@row, OR(@cell = "", @cell <> "")), based on an answer from a previous post.

I'm now stuck on how to implement the INDEX/MATCH part.

Kumiko Percival · 08/01/20

I have been able to get the below to work (although I don't know why it didn't like having [Row ID]@row instead of [Row ID]255—doing so throws up the #NO MATCH error);

=INDEX({Destination Sheet Range 1}, MATCH([Row ID]255, {Destination Sheet Range 3}, 0))

@Andrée StaråI would like to apply IF([email protected]= "Check Box") to the condition of doing the above, if you are able to provide some assistance with this?

Kumiko Percival · 08/02/20

Following on from my comment above, I think I've been able to get the below working

=IF([email protected]<> "", IF(AND([email protected]= "Check Box"), INDEX({Destination Sheet Range 1}, MATCH([Row ID]@row, {Destination Sheet Range 3}))))

The next challenge is integrating it with =IF([email protected]<> "", IF(AND([email protected]<> "Complete",[email protected]<> "Not Required",[email protected]<> "Check Box"), TODAY())) for a different column

Andrée Starå · 08/11/20

Hi@Kumiko Percival

Excellent!

Glad to hear that you found it useful and that you got it working!

Also, I'd be happy to share an example that might give you some other ideas.

Please send me an email at[email protected], and I'll share it with you.

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 help the Community bymarking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

John Sisounthone · 09/04/20

@Andrée Starå

Can you help me build the formula? The formula that I created worked for the first cell that it was inputted but when I drag it down, I would get error messages.

Tina O'Brien · 09/28/20

@Andrée Starå- I'm a total SS noob, so would really love your help setting this up! If you can send me an example I'm happy to give it a go solo.

Thanks!

Tina

Andrée Starå · 09/28/20

@Tina O'Brien

I'd be happy to share an example.

Please send me an email at[email protected], and I'll share it with you.

MarkA. · 10/15/20

@Andrée StaråNo assistance needed from me on this, but I have to compliment the simplicity and elegance of the solution you designed here. In light of the Engage announcements for timekeeping functions, this could really blossom into a powerful use of SS.