Complex IF statement between two sheets with a checkbox

alexis.ray89371

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!!

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.

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.

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. 👍️