# 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

• 163.6 KB Views: 4
• 24 KB Views: 8

#### p45cal

##### Well-Known Member
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.

#### Hany ali

##### Active Member
thanks alot ,Not work With Me ...I Have Excel 2010

#### bosco_yip

##### 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