Complex IF statement between two sheets with a checkbox

Hi,

I am having trouble figuring out how to create a formula. I have one sheet "Step 1-Validations-CBRE" that will contain a full list of vehicle IDs and another sheet "Fleet Inspections" that will contain the submissions for vehicle inspections.

Ultimately, I am trying to determine which vehicles have had inspections completed for the current week and easily see which ones have not. I have the current week formulas working fine. I just now need a formula in the "Step 1-Validations-CBRE" sheet.

I tried to illustrate what I am looking for below. Is anyone able to help me determine the appropriate formula? Thank you!!

Find more posts tagged with

Accepted answers

Paul Newcome

You are going to want something that looks similar to:

=IF(COUNTIFS({Other Sheet Compass Vehicles}, CBRE@row, {Other Sheet Week}, 1) = 1, 1)

Basically we count how many times that particular vehicle has the week box checked in the other sheet. There should only be 1 per vehicle per week, so we say that if that count equals 1, then check the box. If the count does not equal 1 meaning there are no rows in the other sheet that contain that vehicle and a checked box, then leave the box unchecked.

All comments

Paul Newcome

You are going to want something that looks similar to:

=IF(COUNTIFS({Other Sheet Compass Vehicles}, CBRE@row, {Other Sheet Week}, 1) = 1, 1)

alexis.ray89371

Thank you so much! I was trying to make it way harder than it needed to be. Lol!

Paul Newcome

Happy to help. 👍️

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.