Need to identify who responded in the survey

juliusss

I have two sheets. Sheet A is the master list of the names while Sheet B is the list of the respondents of the survey form both containing the unique identifier code. I need to identify who has and has not yet responded to the Smartsheet form. Questions:

1. Is there a way to automatically mark Sheet A when a person submits the form in Sheet B (like in automation workflow)?
2. How can I match to identify the names in Sheet A who have already submitted in Sheet B? Is there a vlookup formula / conditional formatting need to use?

Thank you for all the insights.

Carson Penticuff

The short version is, yes, you can absolutely do this... but there are a few steps to take.

1. You will need a column in "Sheet B" to designate whether the survey has been completed. For this example, a "checkbox" column would be used. If the survey has been completed, the cell will be checked. You could also set it up for a Number/Text column that reads "Responded" or "Not Responded", but the formula would be slightly different.
2. You will need to setup a cross-sheet reference inside of "Sheet A" that points to the column of "Sheet B" that has the unique identifier. https://help.smartsheet.com/articles/2482644-create-cross-sheet-references <-- Here is some information on cross-sheet references, but just respond back if you need additional help.
3. In the checkbox column you setup inside "Sheet B", you will need to enter a formula that checks for the presence of the Unique ID in the other sheet. For this example, I am assuming the column in each sheet is named "Unique ID" and that you setup the cross-sheet reference with the name "Unique ID" as well. You can substitute the actual names in the place of these in the formula.

=IFERROR(IF(COUNTIF({Unique ID}, [Unique ID]@row), 1, 0),"")

juliusss

Hi @Carson Penticuff thanks for checking. What if I want to put the check box option or "Responded/Not Responded" column in Sheet A (masterlist) rather than Sheet B (where the responds go). Can I do that one?

Carson Penticuff

I was confused when entering my response. The solution I posted was meant to describe placing the checkbox in Sheet A., the quote below is corrected. Everything is the same except for the two references to Sheet A. The original formula was also correct. My apologies.

The short version is, yes, you can absolutely do this... but there are a few steps to take.

You will need a column in "Sheet A" to designate whether the survey has been completed. For this example, a "checkbox" column would be used. If the survey has been completed, the cell will be checked. You could also set it up for a Number/Text column that reads "Responded" or "Not Responded", but the formula would be slightly different.

You will need to setup a cross-sheet reference inside of "Sheet A" that points to the column of "Sheet B" that has the unique identifier. https://help.smartsheet.com/articles/2482644-create-cross-sheet-references <-- Here is some information on cross-sheet references, but just respond back if you need additional help.

In the checkbox column you setup inside "Sheet A", you will need to enter a formula that checks for the presence of the Unique ID in the other sheet. For this example, I am assuming the column in each sheet is named "Unique ID" and that you setup the cross-sheet reference with the name "Unique ID" as well. You can substitute the actual names in the place of these in the formula.

=IFERROR(IF(COUNTIF({Unique ID}, [Unique ID]@row), 1, 0),"")