• 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

    Hui...

  • 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
Code:
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))),"")
 

Attachments

  • 1.png
    1.png
    163.6 KB · Views: 6
  • Search.xlsx
    24 KB · Views: 12
In A4 of the Search sheet:
Code:
=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.
 
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))),"")

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