Is there a formula that would check a box if a certain column in a sheet is repeated in another?
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.
Answers
-
Hi @Sydney_Rose
The box formula would be something like this;
=IF(CONTAINS(Name@row, {submission sheet Range Name}), 1)
-
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
-
@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
-
Thank you @Paul Newcome , does this look right to you? Error message has moved from the previous to 'INVALID REF'
-
@Sydney_Rose You need to make sure you are creating your {Cross Sheet Reference} correctly using the below steps:
.
-
@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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 489 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!