Quote:
Originally Posted by NBVC
Do these work?
in AM16 of June sheet:
=IFERROR(INDEX(REMARKS,MATCH(1,INDEX((FULL_NAME=$B 16)*(tDATA[DATE OF TRANSFER OUT/DROP OUT]<=$AC$6),0),0)),"")
copied down
In B16 of July sheet:
=IFERROR(INDEX(FULL_NAME,MATCH(SMALL(IF((COUNTIF($ B$15:$B15,FULL_NAME)=0)*(GRADE=$AC$8)*(SECTION=$AI $8)*(DATE_ENROLLED<=AC$6)*((tDATA[DATE OF TRANSFER OUT/DROP OUT]="")+(tDATA[DATE OF TRANSFER OUT/DROP OUT]>EOMONTH(AC$6,-1)))*(SEX="M"),COUNTIF(FULL_NAME,"<"&FULL_NAME),"" ),1),COUNTIF(FULL_NAME,"<"&FULL_NAME),0)),"")
confirmed with CTRL+SHIFT+ENTER and copied down
|
Thank you for the immediate reply on my questions. The formula in AM16 of June sheet worked like a charm but when I copied it to AM16 of July sheet, the "LE" and "T/I" Remarks in June still appear in July. Sorry, I missed emntioning this on my original query. Only the Remarks occurring during the month should be reflected in the reporting month. The starting date of the month is found in E10, the end date AC6. I added another column in tDATA named DATE_REMARKS, this range contains the date/s of the Remarks. I'm at a loss on how to integrate the start date, end date and DATE_REMARKS in the formula you gave. *(tDATA[DATE_REMARKS]>=$E$10)*(tDATA[DATE_REMARKS<=$AC$6) doesn't work.
Finally, I got the formula right. Again, thank you.