Lock or Store Date/Value Solution without using Zapier

Andrée Starå

Hi all,

I hope you're well and safe!

I've developed a solution that lets you store 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 by marking it as helpful. It will make it easier for others to find a solution.

jg124

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å

@jg124

I'd be happy to share an example. 

Please send me an email at andree@workbold.com, and I'll share it with you.

Kumiko Percival

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

Never mind, I was able to generate a column of unique "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

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(Status@row = "Check Box") to the condition of doing the above, if you are able to provide some assistance with this?

Kumiko Percival

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

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

The next challenge is integrating it with =IF(Status@row <> "", IF(AND(Status@row <> "Complete", Status@row <> "Not Required", Status@row <> "Check Box"), TODAY())) for a different column

Andrée Starå

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 andree@workbold.com, 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 by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

John Sisounthone

@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

@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å

@Tina O'Brien

I'd be happy to share an example. 

Please send me an email at andree@workbold.com, and I'll share it with you.

MarkA.

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

Bill Iuliano

Andrée Starå would be interested in your solution of of using a checkbox to input a date

Andrée Starå

Hi @MarkA.

Thanks so much for the compliment!

Be safe and have a fantastic week!

Best,

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

Andrée Starå

@Bill Iuliano

I'd be happy to share an example. 

Please send me an email at andree@workbold.com, and I'll share it with you.

kellyj

@Andrée Starå Would you be able to share the example you have mentioned here? Thanks much! I'll send you an email :)