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 1ValidationsCBRE" 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 1ValidationsCBRE" sheet.
I tried to illustrate what I am looking for below. Is anyone able to help me determine the appropriate formula? Thank you!!
Best Answer

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.
Answers

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.

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

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!