Automated Formula Question

Benjamin O'Leary

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.

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!

Benjamin O'Leary

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

Benjamin O'Leary

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?

Benjamin O'Leary

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

Benjamin O'Leary

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:

Benjamin O'Leary

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.

Benjamin O'Leary

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å

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