Turn RYG button red or green based on several criteria in a form loaded sheet

Randy Van Winkle

I am creating a dashboard for Vehicle Inspection sheet that I started. The form status collects info from drivers of various vehicles (i.e. what vehicle, checklist, tire pressure etc.). I also included a date column to confirm that any deficiencies are corrected (to be completed later by an admin). I would like to automatically populate a few cells that reference the entire sheet. Any vehicle entry that has a deficiency that has not been confirmed would turn that button red.  If there were initially reported deficiencies, but were verified as corrected the button stays green.

Heather Duff

Randy,

Are you wanting this formula to go in the Vehicle Status column? If so, you could use this:

=if(not(isblank([deficiency corrected]@row)),"Green",if([right front tire]@row="Red","Red",if([left front tire]@row="Red","Red",if([right rear tire]@row="Red","Red",if([left rear tire]@row="Red","Red",if(leaks@row="Yes","Red","Green"))))))

This translates to: If deficiency corrected is not blank, show green. Otherwise, if right front tire is red, show red. Otherwise, if left front tire is red, show red. Otherwise, if right rear tire is red, show red. Otherwise, if left rear tire is red, show red. Otherwise, if leaks is yes, show red. Otherwise, show green.

In other words, if any of the statuses are red (or leak says yes) and there is no correction date, it will show red.

Hope this helps! Let me know if this isn't what you were aiming for.

Best,

Heather

Randy Van Winkle

Thanks for your reply @Heather D . Not sure if I explained thoroughly enough. The example is very simplified. The collection sheet will contain daily submissions so I need to reference the entire columns to look for deficiencies that have not been corrected. There may be 50 rows that show data for Hot Wheels 1 in the vehicle column. I would like to use the vehicle name as the first criteria, then reference all the info that you showed above if any row in the collection sheet meets those criteria

Randy Van Winkle

Does anyone have any help on this?