Automating Responses Between Two Smartsheets

Paul Newcome

Sorry for the delay. I missed the email notification that you had responded. That last one should work with the exception of the quotes. You are using "smart quotes" (slanted). What you need is the ones that are straight up and down which can be achieved by wither retyping the formula directly into your sheet, here in the community, or in a text editor such as Notepad (not Word).

Benjamin O'Leary

@Paul Newcome I made that change, and I'm still getting an #UNPARSEABLE error.

Paul Newcome

Ah. You are missing the square brackets around the column name in the FIND function.

Benjamin O'Leary

@Paul Newcome I'm still getting the same #UNPARSEABLE error. This is what I have:

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

Paul Newcome

Can you show a screenshot of the formula directly in the sheet? The formula you have posted should only be throwing that error if you do not have a column in the same sheet as the formula titled "Labeling Schedule Document #"

Benjamin O'Leary

@Paul Newcome the Document # column is in the Labeling Schedule Smartsheet. I am inputting the formula into the Config Management Smartsheet and referencing the Document # column.

Paul Newcome

That cell reference should be referencing a column on the same sheet as the formula.

Benjamin O'Leary

@Paul Newcome what if I remove the '@cell' portion? Or replace the FIND function with a VLOOKUP function?

Benjamin O'Leary

@Paul Newcome I switched the column values in the formula, thinking that would work:

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

I got the same #UNPARSEABLE error as before. I don't know how to solve this.

Benjamin O'Leary

I feel that the issue I am having has become too complex, and that I did not explain what I am trying to do more clearly:

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

I hope I was able to clarify the issue that I am facing. I have been struggling greatly with creating a formula that would work (much appreciation to @Paul Newcome for getting me as far as he already has). Any and all advice would be greatly appreciated.

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.

Paul Newcome

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

Benjamin O'Leary

@Paul Newcome I'm still getting an #UNPARSEABLE error:

Paul Newcome

Do you have a column called "HelperColumn"?