I am creating an excel document to help make work easier; I work at a non-profit agency. We have multiple families we serve (over 50). For each family, we must track certain things: CPR expiration, annual flu shots, to name a few. I am creating a “family” page for each family that will include everything we must track for each individual family member; I have used conditional formatting to highligh expiration dates and upcoming due dates. I am also creating a main page that I want to show every family and whether each family has anyone with an upcoming expiration date/past due flu shot ect. This main page needs to take into account every family member’s status for each tracked item.
For example, Family A has 3 family members: Apple, Carrot, and Pea. On the family page, it will show that Apple’s annual flu shot was done on 11/15/2017, her CPR expires 11/14/2017, and that her driver’s license expires 10/17/2018. Carrot’s info is: flu shot 09/12/2016, driver’s license 6/10/2018, and CPR 11/12/2016. Pea’s info is flu shot 09/12/2016, driver’s license 4/5/2020, and CPR 12/10/2017. Due to conditional formatting, the family page highlights upcoming due dates (30, 60, and past due) using four colors (good-green, yellow-60days to expiration, orange-30days to expire, red-expired). I need the main page to tell me whether Apple, Carrot, or Pea is in the green, yellow, orange, or red category for each tracked item. If any of them are expired, I need the main page cell to turn red (or say expired), if not expired but within 30 days, for it to turn orange (or say 30days), and to turn yellow if any of them are 60days to expiration. The order should be red, orange, yellow, green (if any are red, it supercedes, orange; if no red, but orange, then orange supercedes yellow and green, ect.)
The whole point of this is to simplify the main page so it’s a snapshot of each family. It’s too cumbersome to have every single person from every single household on one page.
Thanks in advance for any help.