Find MAX value in multiple dynamic ranges

[wdgor]

I need to do a check to see if the user enters a date for the children, in the "Target Completion Date", that is greater than the target date of the parent ("Issue No." that is a whole number represents the parent) - the children have a decimal number in the "Issue No." field and the row is colored. The problem is that there is a variable number of potential children and those can happen with any parent, i.e. there may be no children, 1 child, 2 children, 20 children or X Children. I have a formula =COUNT(IF(MOD($4$1:$a$1000,1),A1:A1000)) that will give me a total count of the decimal entries. I can do a MAX value from that but how can I do a MAX value only looking at each parent and associated child or children? Thanks for any assistance.

 

[wdgor]

The users are aggressively using the sheet and, so far, no complaints and it is producing the desired organization of information and metrics.
Thanks again for your help and patience.

 

[StephenCrump]

The users are aggressively using the sheet .....

Users! What are they like!

 

[wdgor]

They are both client services and development staff. We are using the sheet to both track their tasks and do captivity planning. The Master Worksheet feeds a number of tabs that do capacity metrics and task metrics. It is proving useful to determine how work needs to be distributed and who has bandwidth and who is saturated.