Cross reference a form input against another column?

Loukas W

Is there a way to tie the value of one column to the action of another?

I have a form created, but only certain sites will be responding to it. I thought about making my primary column the site code, but I can't change the column type. So I ended up taking a different column (Column 3), changing it to a dropdown list type, and listing the specific sites I want to respond to this form (approximately 100). The respondent will select their site, input the requested information in the form (Columns 4, 5, and 6), and submit it.

Once the form is submitted, I would like form some way to identify if a certain site has responded or not. I was thinking of using the primary column as the base list, and would like it if the row could be highlighted, or another column that is set to be a checkmark box could automatically be checked when that site is selected in the Column 3 dropdown. I would also like the correct information to be associated with that site's row.

For example, if site ABC completes the form, the row that has ABC in the primary column would change color (or the associated checkmark would be checked), and the information associated with ABC would be added to the appropriate columns.

Can this be done?

Katie G

Probably a lot of ways to solve this! One idea:

Create a "helper" check box column and use a formula that looks through all of column 3 to see if anything matches what is in the primary column of that base list row. If there are one or more matches, check the box (set to true, or 1), otherwise leave unchecked. You could then use conditional formatting based on that row. Would that do what you are imagining?

=IF(COUNTIF([Column 3]:[Column 3], [Primary Column]@row) > 0, true, false)

If you wanted to make this a column formula, you might need to wrap it with something that first checks if there is anything in the Primary Column.

Loukas W

Hi Katie, I tried your formula above, and it worked to get the checkbox checked, but the info that was submitted (Columns, 4, 5, and 6) was added to a new line at the bottom of the sheet. How can I get the info that was submitted to be added to the row that has the Column 3 text in it?

Columns

Checkbox Primary Column Column 3 Column 4 Column 5 Column 6 <-- fields in form (except checkbox)

√ ABC ABC Yes 12/31/2023 No <-- info submitted in form

^^ not a field in form ^^ selected from dropdown field in form

Also, when you say "column formula", I'm not sure what you mean. I added the formula in the cell that is set to be a checkbox, and it worked.

Loukas W

Hopefully this graphic explains it a bit better.

Danielle Arteaga

Hi, Loukas! There isn't really a way to accomplish this on a single worksheet. However, you could set up a "master" worksheet that collects all the form submissions. Then create a workflow that copies rows from the master to separate worksheets for each of your sites.

So, for example, let's say you have Sites A001, B002, and C003. When people submit the form, all the data submitted gets collected on your current worksheet. But, on that sheet, you also have a "Copy a Row" workflow that tells Smartsheet to copy all the rows where Site A001 is selected to the Site A001 worksheet (which you would create). Another condition in that workflow would move rows for Site B002 to the Site B002 worksheet, etc.

You'll end up with one master sheet, with all the aggregated data, then a sheet for each of your sites. This will make your data analytics a LOT easier later.

Here's more on the "copy a row" automation, if you need it: https://help.smartsheet.com/articles/2479626-automatically-move-or-copy-rows-between-sheets

Loukas W

Hi Danielle, I don't think that would work because your solution says that each site needs to have it's own worksheet. I have over 100 sites, so that isn't feasible. I'm ok copying them to a separate sheet, but not 100+ sheets.

If my understanding of your solution isn't correct, please advise.

Katie G

@Loukas W Can you clarify if there will only be one form per site, or many forms? If many, what would you want the top row to show? Is there a reason you wouldn't just want to relay on the bottom rows with the form for the actual data?

You could take an approach of two sheets -- one that has one row per site to make sure they have data + lookup the answers, and a separate sheet with the form answers.

Loukas W

@Katie G Each of the 100+ sites will submit one form (respond to the survey once). I have the sites listed in alphabetical order already in the sheet (Primary Column). All I'm trying to do is make sure that each of the 100+ sites have filled out the form, and collate their responses onto one form. If I need multiple sheets to collect the data, I can do that, but I'd rather not have to make the same sheet and form 100+ times, THEN collate the data from each of the sheets.

If my understanding of your last sentence is correct, two sheets would be manageable, but then how do I get the data from the "data" sheet (where the form responses are collected) to flow into the "results" sheet and make sure that they are filed onto the correct line?

For example, using Danielle's example above, if I have Sites A001, B002, and C003, but C003 responds first, how do I get their response to be copied onto the row in the "data" sheet that corresponds to the C003 row of the "results" sheet?

Katie G

@Loukas W You could make a second sheet, and then use cross sheet references. For example, in the formula I shared =IF(COUNTIF([Column 3]:[Column 3], [Primary Column]@row) > 0, true, false), instead of referencing [Column3]:[Column 3], you can have Smartsheet look at a different sheet.

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

What I'm wondering is if your goal is just to use the top rows to know they are done... once you've gotten all the results in, can you just delete those top rows, and then sort by site, and that have be your final results sheet?

https://help.smartsheet.com/articles/504742-sorting-rows

Loukas W

@Katie G my goal is to use the checkmark column as the indicator to know which sites have completed the form or not. Like I stated above, if I put your formula into a cell on the checkbox column, it works to show the if that site has been selected in the dropdown on the form and the form submitted. However, the form drops the response into the bottom of the sheet, rather than associating it with the value in the primary column.

The values in the primary column are identical to the dropdown values. How do I get the form response to associate with the correct value in the primary column? I am ok with the form responses going to a different sheet, and am ok with the form resposes being copied over to the "data" sheet... but how do I tell the form responses to copy over to correct row where the primary column value and the copied form response value are the same? How do I get the form responses from site B002 to be added to the row that contains "B002" in the primary column of the "data" sheet?

Katie G

@Loukas W Unfortunately, the form results will always go to a new row; you cannot have them directly fill in a previous row, as nice as that would be. So you'll just have to do some sort of work around... I'm thinking the easiest is to just fill in the Primary column with the same value as the drop down for the new rows added by form, and delete the placeholder row at the top. Not ideal but hopefully not too much extra work.

The other option is to force it to look up the form results for every row, then you end up with two rows per site.