Edit: The formula addresses the situation given in Project Description. I notice that there are couple of more situations that need to be addressed and the formula below will have to be updated to cover these as well.
Greetings!
I have worked on the problem. Please use this formula in cell F10 and copy the same from all table cells from row 10 to row 18. For row 9, existing formula is sufficient.
IF(AND(F$8>=$C10,F$8<=$D10),IF((INDEX('Available '!$A$2:$M$12,MATCH('work sheet'!$B10,'Available '!$A$2:$A$12,0),MATCH('work sheet'!F$8,'Available '!$A$2:$M$2,0)))-(INDEX(Record!$A$1:$M$11,MATCH('work sheet'!$B10,Record!$A$1:$A$11,0),MATCH('work sheet'!F$8,Record!$A$1:$M$1,0)))<(INDEX($F$3:$Q$5,MATCH($A10,$E$3:$E$5,0),MATCH(F$8,$F$8:$Q$8,0))*$E10),(INDEX('Available '!$A$2:$M$12,MATCH('work sheet'!$B10,'Available '!$A$2:$A$12,0),MATCH('work sheet'!F$8,'Available '!$A$2:$M$2,0)))-(INDEX(Record!$A$1:$M$11,MATCH('work sheet'!$B10,Record!$A$1:$A$11,0),MATCH('work sheet'!F$8,Record!$A$1:$M$1,0))),MIN((INDEX('Available '!$A$2:$M$12,MATCH('work sheet'!$B10,'Available '!$A$2:$A$12,0),MATCH('work sheet'!F$8,'Available '!$A$2:$M$2,0)))-SUM(F$9:F9),INDEX($F$3:$Q$5,MATCH($A10,$E$3:$E$5,0),MATCH(F$8,$F$8:$Q$8,0))*$E10)),"")
Please try it out and let me know your feedback.
Have a Great Weekend!
Srinivasan