No Drop Down entry for the week

Options

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.

Best Answer

  • Zachary Hall
    Zachary Hall ✭✭✭✭✭✭
    Answer ✓
    Options

    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.

    Best,

    Zach Hall

    Training Delivery Manager / Charter Communications

Answers

  • Zachary Hall
    Zachary Hall ✭✭✭✭✭✭
    Answer ✓
    Options

    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.

    Best,

    Zach Hall

    Training Delivery Manager / Charter Communications

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!