Lock or Store Date/Value Solution without using Zapier

Lock or Store Date/Value Solution without using Zapier

Andrée StaråAndrée Starå ✭✭✭✭✭
edited 05/24/20 in Formulas and Functions

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

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Comments

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

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • 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

Sign In or Register to comment.