No Drop Down entry for the week

strobn

I am looking for a formula to indicate if there are no entries of an item in a drop down menu within a particular week of the year. Below is a brief rundown of what I have accomplished, and what I desire.

I am looking to create a report or dashboard to reflect if an inspection on a particular device has been completed for the week, as will as it's pass or fail status.

I have have 6 devices that are assigned to a column (drop down), a column for pass/fail (created by formula), Created column (auto populate), and then added a week (formula from created) and a year column (formula from created).

I am unsure of how to created a pivot type table to check to see if an inspection was completed in a particular week. (At least that is what my brain believes I should do.)

The below image is shows the current set up in Google Sheets of what we have been seeing.

Green (P) = Pass, Black (ND) = Not Done, Red (F) = Failed (Not reflected on image)

While it does not need to be in this formatting, I am looking for a way to easily view a year at a time, and check those three statuses for each device.

While not ideal, I could view like this, but would want it to reflect in the device column each of the 6 devices, even if an entry has not occurred in the designated week.

Thanking you in advance.

Zachary Hall

You could generate exactly what you are looking for (if you don't mind the number of inter-sheet references) by creating another sheet where you pull the data over using INDEX/MATCH.

Zachary Hall

You could generate exactly what you are looking for (if you don't mind the number of inter-sheet references) by creating another sheet where you pull the data over using INDEX/MATCH.