I have Sheet with schedules of the customers and date for delivery but i have couple sheets like that.
I want to create a calendar that brings all the sheets together, so We can see everything in the calendar on the appropriate date.
I am using this code. =IF(CURRENT!H20:H117=E1,CURRENT!B20:B117,"")
problem with this is, this works only on one sheet and also when it prints on the calendar I have a lot of empty rows.
I am basically looking for a better idea to approach this or a fix for the above code.
it jumps rows because where it gets the information does not have the same dates.
CodePudding user response：
Lazy Method
you can create a second page and use an array formula as below,
=IFERROR(INDEX(B$4:B$12,SMALL(IF(B$4:B$12<>"",ROW(B$4:B$12)-ROW(B$4) 1),ROWS(D$4:D4))),"") CTRL SHIFT ENTER
Excel - Array Formula to skip empty cells
Proper Method
=IFERROR(INDEX(CURRENT!$B$1:$B$5,SMALL(IF(CURRENT!$A$1:$A$5=Sheet2!A$1,ROW(CURRENT!$A$1:$A$5)-1),ROW()-ROW($A$2) 1),0),"") CTRL SHIFT ENTER