Automating Responses Between Two Smartsheets

Benjamin O'Leary

@Paul Newcome Range 3 is the Document # column in the Labeling Schedule sheet. Range 4 is the Status column in the Labeling Schedule sheet.

Paul Newcome

I'm sorry, but I'm lost. Are you trying to pull the status to the Config Sheet or the Labeling Sheet? You have both listed in various screenshots, so I am not sure where you are pulling the data from and where you are trying to put it.

Benjamin O'Leary

@Paul Newcome the overall goal of the project is to allow users to make a change in the Labeling Schedule Status column, and have the Config Management Labeling Progress column automatically update based on said changes to the Labeling Schedule Status column.

To do this, we want to link-up the fields present in the Labeling Schedule Document # column with the data present in the Config Management Helper column. However, the Config Management Helper column has multiple strings of data from multiple columns within the Config Management sheet, so we can't do a direct 1:1 link between the Labeling Schedule Document # column and the Config Management Helper column.

We want to be able to take all of the data within the Config Management Helper column and see if any of that data is present in the Labeling Schedule Document # column. If there is a match, then there would be a link for that particular row(s), and then we can automate the process between the Labeling Schedule Status column and the Config Management Labeling Progress column.

I understand this is very complex. Please let me know if there is anything else I can clarify.

Paul Newcome

Are you able to provide new screenshots? The reason I ask is because some previous screenshots show the linking formula going in the Config sheet and other screenshots show the linking formula going in the Labeling sheet.

Benjamin O'Leary

@Paul Newcome Sure. For the Config Management sheet, there's a screenshot of the Helper Column that contains all of the text strings present in the other columns (Carton, Labels, UDI, etc.). There's also a screenshot of the formula I am trying to build in the Labeling Schedule Progress column (so that when someone makes a status change in the Labeling Schedule sheet, the Labeling Schedule Progress column will update accordingly automatically).

And the Labeling Schedule screenshot shows the Status and Document # columns (users will be updating the Status column, and if the value in the Document # column is contained in the Config Management Helper Column, then the Labeling Schedule Progress column will automatically update as well).

Copy of Config Management Helper Column.png

Copy of Config Management Labeling Schedule.png

Copy of Labeling Schedule Status and Doc #.png

Paul Newcome

Ok. So that takes me back to my previous question of... What if there are multiple different statuses in the Labeling sheet?

Example: Config sheet has "A", "B", and "C" in a single row across the different columns. The Labeling sheet has "A" listed as "In Progress", "B" listed as "Not Started" and "C" listed as "Complete". What status should be pulled to the Config sheet.

When I had initially asked you this, you said that was not a possibility. You provided a screenshot afterwards to show the formula that wasn't working, and the formula was in the Labeling sheet. That's where things got confused.

Benjamin O'Leary

@Paul Newcome Yes, at first we were trying to see if there was a way for a user to make a change to the Status column in the Labeling Schedule sheet, and have the subsequent cell in the Labeling Progress column in the Config Management sheet update automatically. Thanks to your help, we were able to create a formula that performs said automation if there was only one text string per row in the Config Management sheet.

Now, the client wants to see if there is a way to do that exact same process, but where there is multiple text strings in multiple columns per row in the Config Management sheet. In both cases the Labeling Schedule Document # column will only have one text string per row.

I'm not sure if there is a way to do this, since, as you said earlier, updating the Status for a particular Document # in the Labeling Schedule sheet will update the Labeling Progress column in the Config Management sheet, even if there are multiple text strings per row and only one text string is meant to be updated. Do you know of any way to possibly circumvent this? We cannot make any adjustments to the Config Management sheet, so we can't create multiple rows so there aren't multiple text strings in multiple columns in each row.

Again, I apologize for any confusion. This is a very complicated project.

Paul Newcome

The only 2 options I can think of would be to display only a single value based on the first match or to join each of the statuses from the labeling sheet to the Config sheet based on a match of each string within the row. Both solutions are going to have some differences from each other though, so please let me know which you prefer and I'll be happy to help you work through something.

Benjamin O'Leary

@Paul Newcome would either of these options require significant changes to the Config Management sheet? Because we cannot make any significant format changes (for example, we cannot take a row with multiple text strings in multiple columns and add additional rows so that each text string has its own row).

Paul Newcome

Neither would require any significant changes. It is just a different set of functions that is used to create the formula that would be used.

Benjamin O'Leary

@Paul Newcome how would I go about implementing the second option (join each of the statuses from the Labeling sheet to the Config sheet)?

Benjamin O'Leary

@Paul Newcome I tried to join the the two status columns between the Labeling and Config sheets using the following formula:

=JOIN(DISTINCT({Copy of Labeling Schedule Range 4}))

I didn't get any errors, but it did add an additional Status option on the Config Sheet (see screenshot).

Any idea what this could be?

New Status.png

Paul Newcome

You are going to want to use a COLLECT function as well.

=JOIN(DISTINCT(COLLECT(

Benjamin O'Leary

@Paul Newcome so =JOIN(DISTINCT(COLLECT({Copy of Labeling Schedule Range 4})))? Because I'm getting an Incorrect Argument error.

Benjamin O'Leary

@Paul Newcome I've been working on the formula, and I think I am making some progress. Could you let me know what you think?

=JOIN(DISTINCT(COLLECT({Copy of Labeling Schedule Status}, {Copy of ConfigManagement Helper Column},FIND(@cell,Document #@row)>0)),”, ”)