Pivot Table: Return Value when <5 and show remaining

**Flyinace2000** · 12-10-2012, 08:23 AM

I have a resource table with week start as column headers and people's names as row starts. Each cell then has how many days they are funded for. I need a pivot table to show when the resource goes below 5 days during a given week and how many days are left in the profile from Today()'s date.

**dilipandey** · 12-15-2012, 02:36 PM

HI Flyinace,

See the below file where I used formula:-

Formula:

=IF(INDEX($A$1:$L$1,1,MIN(IF(($B2:$L2<5)*($B2:$L2<>""),COLUMN($B2:$L2),"")))<=0,"",INDEX($A$1:$L$1,1,MIN(IF(($B2:$L2<5)*($B2:$L2<>""),COLUMN($B2:$L2),""))))

Enter formula using ctrl shift enter key combination

Resource Highlighting.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>