Alert for Percentages in Column

Mickey McCommons

I have a sheet with people and each row lists their name and the project name and the percentage of their time they spend on that project each month. If they work on more than one project they will show up in multiple rows. A separate row for each project they work on. I am trying to find a way where I can get an alert or conditional formatting when that person's total percentage in the column exceeds 100%. Since all staff are on this sheet it can't just SUM the column in whole. Seems like a formula would be able to do this but I cannot figure it out. Any ideas? Thanks.

Mickey

Paul Newcome

I would suggest a second sheet where each person is listed out only once. Then you can use a SUMIFS with a cross sheet reference to get the total utilization on this "alert sheet" and send your notification from there.

Conditional formatting can be applied to the source sheet by using a helper column that pulls in the total from the "alert sheet" using an INDEX/MATCH on every row and then basing your conditional formatting on this helper column.