I am assuming 'Amin' in cell E5 is a typo and should be 'Admin'.
This is a bit involved. It makes heavy use of Name Manager (to reduce formula bloat) and a re-dimensioning strategy I adapted from Lori here:
https://excelxor.com/2016/04/08/adva...le-worksheets/
This named formula I call WorkTypesFilter. It pre-filters the relevant data in AJ_WorkType.
Formula:
=(Table1[Client]='SUMMARY SHEET'!$B$3)*(Table1[Job]='SUMMARY SHEET'!$B$5)*(Table1[[1]:[10]]<>"")
This named formula I call WorkTypes_Addresses. It returns the cell addresses of the relevant AJ_WorkType.
Formula:
=BASE(MODE.MULT(IF((AJ_WorkTypes<>"")*(AJ_Client='SUMMARY SHEET'!$B$3)*(AJ_Job='SUMMARY SHEET'!$B$5),DECIMAL(ADDRESS(ROW(AJ_WorkTypes),COLUMN(AJ_WorkTypes),4),36)),DECIMAL(ADDRESS(ROW(AJ_WorkTypes),COLUMN(AJ_WorkTypes),4),36)),36)
The final formula is array entered in H6 and filled down.
Formula:
=IFERROR(INDEX(T(INDIRECT("'Table of Data'!"&WorkTypes_Addresses)),SMALL(IF(FREQUENCY(MATCH(T(INDIRECT("'Table of Data'!"&WorkTypes_Addresses)),T(INDIRECT("'Table of Data'!"&WorkTypes_Addresses)),0),ROW(INDIRECT("1:"&SUMPRODUCT(WorkTypesFilter)))),ROW(INDIRECT("1:"&SUMPRODUCT(WorkTypesFilter)))),ROWS($2:2))),"")
Bookmarks