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.
Answers
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 377 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!