How to change colour to one cell based on expiration status of multiple cells in a different sheet
I’m working on an excel training matrix for my team, and I managed to get all training divided by type (if university based, hospital based or team specific). Each training category has a different sheet.
I would like to add a main page where I have all team members names on the Y axis and all categories of training on the X axis, that reads for each team member’s row in the specific training’s sheet, and reports green if all training in that sheet is up to date, yellow if something is about to expire, and red if something has expired.
Basically I would like it to be more evident at a glance if someone has something to update rather than check each spreadsheet (we have tons of trainings).
I was thinking of a formula that goes
IF [PERSONNEL ALL SPECIFIC ROW IN SHEET TRAINING 1] is GREEN then [CURRENT CELL IN MASTER SHEET] is GREEN
IF [PERSONNEL ALL SPECIFIC ROW IN SHEET TRAINING 1] is YELLOW then [CURRENT CELL IN MASTER SHEET] is YELLOW
IF [PERSONNEL ALL SPECIFIC ROW IN SHEET TRAINING 1] is RED then [CURRENT CELL IN MASTER SHEET] is RED
(hope this makes sense, I am useless with excel formulas…)
I would then be able to just change the sheet training row and name to have the rest of the document auto populate itself.
Can someone help? Is this something doable with Excel?
I’m working on an excel training matrix for my team, and I managed to get all training divided by type (if university based, hospital based or team specific). Each training category has a different sheet. I would like to add a main page where I have all team members names on the Y axis and all categories of training on the X axis, that reads for each team member’s row in the specific training’s sheet, and reports green if all training in that sheet is up to date, yellow if something is about to expire, and red if something has expired.Basically I would like it to be more evident at a glance if someone has something to update rather than check each spreadsheet (we have tons of trainings). I was thinking of a formula that goesIF [PERSONNEL ALL SPECIFIC ROW IN SHEET TRAINING 1] is GREEN then [CURRENT CELL IN MASTER SHEET] is GREENIF [PERSONNEL ALL SPECIFIC ROW IN SHEET TRAINING 1] is YELLOW then [CURRENT CELL IN MASTER SHEET] is YELLOWIF [PERSONNEL ALL SPECIFIC ROW IN SHEET TRAINING 1] is RED then [CURRENT CELL IN MASTER SHEET] is RED(hope this makes sense, I am useless with excel formulas…)I would then be able to just change the sheet training row and name to have the rest of the document auto populate itself. Can someone help? Is this something doable with Excel? Read More
