• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Modify the formula for fetching all absence dates for each employee

Hany ali

Active Member
Hello My Masters ,I Want Your Help to Modify the formula for fetching all absence dates for each employee For $D$4:$AH$4 From Attendance and Absent Sheet To Search Sheet Start from A4
IFERROR(INDEX('Attendance and Absent'!$D$4:$AH$4,SMALL(IF('Attendance and Absent'!$B$6:$B$38=A$3,IF('Attendance and Absent'!$D$6:$AH$38="A",ROW(D$6:AG$535)-ROW(D$6)+1)),ROWS($A$4:A4))),"")



Well-Known Member
In A4 of the Search sheet:
=TRANSPOSE(FILTER('Attendance and Absent'!$D$4:$AH$4,INDEX('Attendance and Absent'!$D$6:$AH$38,MATCH(A$3,'Attendance and Absent'!$B$6:$B$38,0),)="A","No absences"))
and copy across. Clear cells below row 4 to allow the results to spill.


Excel Ninja
Maybe, try this formula can use in your Excel 2010.

In A4, formula copied across right and down :

=IFERROR(INDEX('Attendance and Absent'!$D$4:$AH$4,AGGREGATE(15,6,COLUMN('Attendance and Absent'!$D$4:$AH$4)-COLUMN('Attendance and Absent'!$C$4)/(INDEX('Attendance and Absent'!$D$6:$AH$38,MATCH(A$3,'Attendance and Absent'!$B$6:$B$38,0),0)="A"),ROW($A1))),"")


Hany ali

Active Member
Well done our honorable professor ... excellent and this is indeed what is required. Thank you very much for your presence