Check box if multiple conditions are met across 2 sheets

Good morning,

I have 2 sheets

Sheet A: Job #, complete(Checkbox)

Sheet B: Job #, Reason, complete(Checkbox)

The goal is to have a formula that will check the box in the complete column on Sheet A if the Job # matches between the 2 sheets, the Reason column on Sheet B is "Screens", and the complete column on Sheet B is checked.

This is my current formula

=IF(AND(MATCH([Job #]@row, {Sheet A Job #}), {Sheet B Reason} = "Screens", {Sheet B Complete} = 1), 1)

This is the result I'm getting

#INVALID DATA TYPE

I feel like I'm missing something obvious, or maybe there is a completely different way to do this. Any help is appreciated, Thanks!

Find more posts tagged with

Formulas

Accepted answers

Adam Murphy

Try =IF(COUNTIFS({Sheet A Job #}, [Job #]@row, {Sheet A Job #}, @cell = "Screeens", {Sheet B Reason}, @cell = "1", {Sheet B Complete}) > 0, 1, 0)

All comments

Adam Murphy

Try =IF(COUNTIFS({Sheet A Job #}, [Job #]@row, {Sheet A Job #}, @cell = "Screeens", {Sheet B Reason}, @cell = "1", {Sheet B Complete}) > 0, 1, 0)

Mark.Hendley

@Adam Murphy Of course as soon as I post this I found the answer from another discussion despite spending all morning looking at discussions, your answer is the same answer I got from another post so thanks for confirming it!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.