I am struggling with two issues in the same sheet, related to one another. This sheet has grown over time and is incredibly useful but could be even better.
I have a spreadsheet that contains many projects' resource allocation. I have a series of columns/rows that count those resources, compare them to availability, PTO, etc. and display counts and conflicts (summary table).
The first issue I have is when I am looking at this summary table, I am unable to see if a resource is traveling, on site, etc during any given time (it just displays the allocation count on any given day and alerts if double allocated or allocated on a day that has PTO) and I have to therefore go find the resource in their relative project to see this info. We try to avoid allocating resources two weeks in a row but if one of the weeks is remote allocation then we do not mind. IF I could inherit the color from the cell in the countif statement (ie =COUNTIF($BO137:$ED137,AL$4)) in the summary table, then at a glance I would see the status of the resource based on the color assigned in any given project columns and could see travel days (static unique color), on site days (static unique color), remote days (static unique color).
The second issue is I want to conditionally format the same summary row if the resource is allocated (so the value of the summary row for any given resource) is >0 any of the 7 days (or 7 cells) before the given cell in a column. This will let me see if the resource is allocated two weeks in a row which we want to avoid ONLY if the preceding cells are NOT remote (see above).
Bookmarks