Lock or Store Date/Value Solution without using Zapier

Andrée StaråAndrée Starå ✭✭✭✭✭
edited 08/27/21 in Best Practice
05/24/20 Edited 08/27/21

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.

SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Previous1

Comments

  • jg124jg124 ✭✭✭✭✭

    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åAndrée Starå ✭✭✭✭✭

    @jg124

    I'd be happy to share an example. 

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

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • 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!

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

  • 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?

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

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

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

  • @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åAndrée Starå ✭✭✭✭✭

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

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • @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 IulianoBill Iuliano ✭✭✭✭

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

  • Andrée Starå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

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    @Bill Iuliano

    I'd be happy to share an example. 

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

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected]om | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • kellyjkellyj Employee

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

Sign In or Register to comment.