Is there a formula that would check a box if a certain column in a sheet is repeated in another?

Sydney_Rose

Hello! I am trying to create a cross-referencing set-up in my sheet. I will be generic in my explanation:

I have two sheets - one with a tracking sheet that tracks submissions, where the left column is a checkbox. The other sheet is where I get updates on submissions as they come in. I update the submissions on an Excel sheet, which is transferred to the update sheet through Data Shuttle.

I am trying to have the box check itself if the submission sheet 'name' is on the tracking sheet, i.e. if the form is submitted and the name pops up on my submission sheet, then the box will check itself on the tracking sheet.

jmyzk_cloudsmart_jp

Hi @Sydney_Rose

The box formula would be something like this;

=IF(CONTAINS(Name@row, {submission sheet Range Name}), 1)

https://app.smartsheet.com/b/publish?EQBCT=c575ac55c0524274a7526e655dffe595

Sydney_Rose

This is the result, received as #UNPARCEABLE.

'COI Updates' is the referenced sheet (from Data Shuttle) and the 'COI Tracking' would be the tracking sheet where I want to check the boxes. The 'COI UPDATES RANGE SUBMITTER EMAIL' is column 3 on my COI Updates sheet, and 'CONTACT EMAIL' is column 15 on my COI Tracking sheet. Hoping the details may help with the formula. Thank you

Paul Newcome

@Sydney_Rose When referencing a column that has spaces, numbers, and/or special characters, you have to wrap the column anme in [Square Brackets].

[Contact email]@row

Sydney_Rose

Thank you @Paul Newcome , does this look right to you? Error message has moved from the previous to 'INVALID REF'

Paul Newcome

@Sydney_Rose You need to make sure you are creating your {Cross Sheet Reference} correctly using the below steps:

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

.

Sydney_Rose

@Paul Newcome after lots of help from y'all and ChatGPT I am using this formula in a helper column:

=IF(COUNTIF({COI Updates Range 1}, [Contact Email]@row) > 0, "✓", "")

and this formula in the checked box column:

=IF([Exists in COI Updates]@row = "✓", 1, 0)

Everything is now running smoothly. Thanks!