Automating Responses Between Two Smartsheets

Benjamin O'Leary

Hello,

I have two sheets that I am trying to connect in a specific way. The one sheet, Config Mgmt, has a Labeling Progress column with two dropdown options and six additional columns where users can input text. The other sheet, Labeling Sched, has a Document # column where users can input text and a Status column, which has the same two dropdown options in the Config Mgmt sheet.

My client wants to set up an automation process where if any of the text strings present in any of the six text columns in the Config Mgmt sheet match the text string present in the Labeling Sched sheet, and the user makes an update to the Status column, then the Labeling Progress column will automatically update as well.

I understand that this is most likely a very complicated task, so I attached two screenshots that will hopefully help.

Regards,

Benjamin O'Leary

Screenshot 2021-09-23 114056.png

Screenshot 2021-09-23 114121.png

Paul Newcome

My apologies. I misunderstood where you were wanting the formula.

Put this on the Config Sheet in a helper column:

=JOIN(COLLECT(PI@row:[Other Purchased Items]@row, PI@row:[Other Purchased Items]@row, @cell <> ""))

Then the formula in the Labeling sheet will be:

=INDEX({Config Sheet Status Column}, MATCH([Document #]@row, {Config Sheet Helper Column}, 0))

Paul Newcome

Will the Config sheet ever have a row where there is something in more than 1 of the 6 columns?

Benjamin O'Leary

@Paul Newcome yes, I believe users will be able to input multiple text strings to multiple columns (or not input anything at all) in the Config Mgmt sheet.

Paul Newcome

Ok. So what if there are two entries in row 1 of the Config sheet and in the Labeling sheet those two entries have different statuses?

Benjamin O'Leary

@Paul Newcome We are planning on creating a column in the Labeling Sched sheet that has a dropdown of the columns in the Config Mgmt sheet called 'Doc Type'.

So, for example, if one row in the Labeling Schedule Smartsheet has Carton under the 'Doc Type' column, that means that the subsequent label present in the Config Management sheet has data under the 'Carton' column.

This way, there shouldn't be more than one entry per row in the Config Mgmt sheet.

Paul Newcome

If there will never be more than one in a row on the Config sheet, then I would suggest this...

=INDEX({Label Sheet Status Column}, MATCH(JOIN(COLLECT(PI@row:[Other Purchased Items]@row, PI@row:[Other Purchased Items]@row, @cell <> "")), {Label Sheet Doc # Column}, 0))

Benjamin O'Leary

@Paul Newcome I tried the INDEX formula, and I got an #UNPARSEABLE error (please see attached screenshot)

Unparseable Screenshot.png

Paul Newcome

My apologies. I misunderstood where you were wanting the formula.

Put this on the Config Sheet in a helper column:

=JOIN(COLLECT(PI@row:[Other Purchased Items]@row, PI@row:[Other Purchased Items]@row, @cell <> ""))

Then the formula in the Labeling sheet will be:

=INDEX({Config Sheet Status Column}, MATCH([Document #]@row, {Config Sheet Helper Column}, 0))

Benjamin O'Leary

@Paul Newcome now I'm getting an INVALID REF error:

Invalid Ref Screenshot.png

Paul Newcome

How exactly did you set up your cross sheet references?

Benjamin O'Leary

@Paul Newcome I managed to get the formula to work. Thank you so much for the assistance!

Paul Newcome

Happy to help. 👍️

Benjamin O'Leary

@Paul Newcome I have another issue pertaining to this. The Config Mgmt sheet has multiple Document #'s per row, whereas each row in the Labeling Sched sheet only has one Document #. I won't be able to get a 1:1 match between the two. I'm trying to see if a CONTAIN formula will work instead of an INDEX formula, but I'm not sure how to go about setting it up.

Paul Newcome

Ok. That's why I asked if there would be multiples on a single row. You are going to need to change the INDEX formula over to something more like this...

=INDEX(COLLECT({Config Sheet Status Column}, {Config Sheet Helper Column}, CONTAINS([Document #]@row, @cell)), 1)

Benjamin O'Leary

@Paul Newcome I tried that, and I'm getting another #UNPARSEABLE error

Unparseable Screenshot 2.png

Paul Newcome

What is in "[Copy of Labeling Schedule Range 3]@row"?