Format Cells Based on Given Input

[Excel Fox]

Here's one way to do it

=IFERROR(SUMPRODUCT((C$3:I$3>=INDEX(Sheet1!$F$4:$F $8,MATCH($B12,SalesPersons,0)))*(C$3:I$3<=INDEX(Sh eet1!$G$4:$G$8,MATCH($B12,SalesPersons,0)))*(WEEKD AY(C$3:I$3,2)<6)*ISNA(MATCH(C$3:I$3,Holidays,0))), 0)

[Rajesh Kr Joshi]

WOW...this is awesome.....it is work for me...Let me implement this and surely share the feedback with you .

Thanks a ton
Rajesh

[Rajesh Kr Joshi]

Sample Allocation.xlsxUtilization.xlsx

Hi, I tried this and it worked great for the cell highlighting portion..but i was failed to implement the formula to calculate the days ... that may be because i was trying to refelect the days in a diffrent sheet. I am attaching both the file i worked on. The Utilization file is the one i wants to refelect the days and sample allocation is the plotter which is working fine....also is there s way that if some one works on sat or sunday that can also be refelect in the plotter.

Thanks in anticipation.
Rajesh

[Excel Fox]

Use this in D7 of utilization sheet

=SUMPRODUCT(('[Sample Allocation.xlsx]Plotter'!$K$4:$Q$4>=INDEX('[Sample Allocation.xlsx]Plan'!$H$4:$H$9,MATCH($B7,'[Sample Allocation.xlsx]Plan'!$G$4:$G$9,0)))*('[Sample Allocation.xlsx]Plotter'!$K$4:$Q$4<=INDEX('[Sample Allocation.xlsx]Plan'!$I$4:$I$9,MATCH($B7,'[Sample Allocation.xlsx]Plan'!$G$4:$G$9,0)))*ISNA(MATCH('[Sample Allocation.xlsx]Plotter'!$K$4:$Q$4,'[Sample Allocation.xlsx]Plan'!$R$3:$R$4,0)))