Automated Formula Question

I have two Smartsheets: Config and Labeling. Both Smartsheets have Document #'s in them. The Config Smartsheet has multiple Document #'s in each row, in different columns, while the Labeling Smartsheet has one Document # in each row, all in one column. Both Smartsheets also have a Status column with the same dropdown options.

What I want to do is to connect the two Status columns, so if someone changes a status in the Labeling Smartsheet, the subsequent status in the Config Smartsheet changes automatically. This would be done by matching the Document # fields in each Smartsheet (so the status fields will connect if the Document #'s in each Smartsheet are the same for that row).

The issue I am facing is that the Config Smartsheet has multiple Document #'s per row, so I cannot do a straight 1:1 match. To help mitigate this issue, I created a Helper Column in the Config Smartsheet that joins all of the Document #'s present in each row to one cell (each Document # is separated by a comma).

The formula that I am trying to create in the Config Smartsheet Status column will look through the Document #'s in the Config Smartsheet Helper Column, and, if one of the Document #'s in that column matches a Document # in the Labeling Smartsheet, create a Status connection for those particular rows.

Any suggestions on how to do this would be greatly appreciated.

Tags:

Answers

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

    Hi @Benjamin O'Leary

    I hope you're well and safe!

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    I hope that helps!

    Be 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 support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • @Andrée Starå Here is a screenshot of the Config Smartsheet:

    The column highlighted in yellow is where the formula will be added. The text strings that are highlighted in red are the Document #'s. As you can see, there are multiple Document #'s per row.

    Here is another screenshot of the Config Smartsheet, which shows the Helper Column containing a formula that puts all of the Document #'s from each row into one cell, separated by commas:

    Here is a screenshot of the Labeling Smartsheet:

    The column highlighted in yellow contains the same dropdown options as the yellow-highlighted column in the Config Smartsheet. I also underlined the Document #'s present in the sheet; they are the same as in the Config Smartsheet, except for the fact that there aren't multiple Document #'s per row.

    I am ultimately trying to connect the two yellow-highlighted columns in each Smartsheet, based on if there is a match among Document #'s. Please let me know if there is any additional information I can provide you.

    Thank You!

  • I have been using this formula, and it seems to working somewhat:

    =JOIN(DISTINCT(COLLECT({Copy of Labeling Schedule Status}, {Copy of Labeling Schedule DocNum}, FIND(HelperColumn@row, @cell) > 0)))

    Am I heading in the right direction with this formula?

  • @Andrée Starå any suggestions on what I should do to to resolve this issue? Everything I have tried so far has not worked.

  • I played around with the formula:

    =JOIN(COLLECT({Copy of Labeling Schedule Status}, {Copy of Labeling Schedule Doc #}, MATCH(HelperColumn@row, @cell) > 0))

    And am now getting a #No Match error:


  • I also tried combining a JOIN function with an INDEX function, since it seems to work when there is only one Document # in the row in the Config Management sheet:

    =JOIN(DISTINCT(INDEX({Status}, MATCH(HelperColumn@row, {DocNum}, 0)),'',''))

    Any feedback would be greatly appreciated.

  • My team have been trying this formula out:

    =JOIN(COLLECT({Copy of Labeling Schedule Status}, {Copy of Labeling Schedule Doc #}, HAS(@cell,HelperColumn )), " , ")

    And we're not sure why it isn't working.

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

    Hi @Benjamin O'Leary

    I hope you're well and safe!

    Thanks for your patience!

    How did it go? Did you manage to get something set up?

    I hope that helps!

    Have a fantastic weekend & Happy New Year!

    Best,

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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!