Filtering Data in Multiple Tables with the Filter Function (Excel)

Question

I'm trying to return data from multiple tables based on a date. Each table has employee schedules with different tasks and I'd like to join these on a master output sheet that is dynamic with a formula.

=IF(A27=I27,FILTER(Employee 1,(Employee 1[Day]=D27)*(Employee 1[Name]=A27),"NA"),IF(A27=I28,FILTER(Employee 2,(Employee 2[Day]=D27)*(Employee 2[Name]=A27),"NA")

The above formula works if I toggle the employees name in a drop down list, but I'd like to have a "All" option that pulls in all of the employee names into the filter.

Something like this (the syntax is not correct):

=FILTER(Employee 1,Employee 1[Day]=D27),FILTER(Employee 2,(Employee 2[Day]=D27)))

Here is a photo of the summary I'm trying add a "all" option in the drop down. [1]enter image description here

Any help would be greatly appreciated. Thank you in advance!

Answer 1

Very 1st of All, You are not allowed to use space in table names So, i changed your table names from Employee 1 and Employee 2 To Employee_1 and Employee_2 Now You can Use this formula to get the required result

=LET(name1,I27,name2,I28,tabname1,Employee_1[Name],tabname2,Employee_2[Name],daycheck1,Employee_1[Day]=D27,daycheck2,Employee_2[Day]=D27,x, FILTER(Employee_1,(daycheck1)*(tabname1=name1)), y, FILTER(Employee_2,(daycheck2)*(tabname2=name2)),rows1, COUNTIFS(tabname1,name1,Employee_1[Day],D27), rows2, COUNTIFS(tabname2,name2,Employee_2[Day],D27),myrows, SEQUENCE(rows1+rows2),cols,(IFERROR(COLUMNS(x),COLUMNS(y))),mycols, SEQUENCE(1,cols),IF(A27=I27,x,IF(A27=I28,y,IF(A27="All Employees",IF(myrows<=rows1, x, INDEX(y, myrows-rows1, mycols)),"NA"))))

Hope this works for you