I am using the following formula
=LET(T,HSTACK(Tina!$B$4:$I$9962,Tina!$AO$4:$AO$9962,Tina!$AV$4:$AV$9962),Z,HSTACK(Zarah!$B$4:$I$9973,Zarah!$AO$4:$AO$9973,Zarah!$AV$4:$AV$9973),M,HSTACK(Michael!$B$4:$I$9993,Michael!$AO$4:$AO$9993,Michael!$AV$4:$AV$9993),S,HSTACK(Shin?!$B$4:$I$9985,Shin?!$AO$4:$AO$9985,Shin?!$AV$4:$AV$9985),d,VSTACK(T,Z,M,S),FILTER(d,(INDEX(d,,8)="New Hire")*(INDEX(d,,9)=TRUE)*(INDEX(d,,6)="Jobfit")*NOT((INDEX(d,,7)="Casual")+(INDEX(d,,7)="Fixed Casual"))))
This formula is pulling the data off 4 spreadsheets (Shine, Tina, Zarah, Michael). The data is new employees and it has their details (only if value equals TRUE (meaning the onboard is completed)) then finally it pulls their commencement date off the 4 spreadsheets. I want the collated array of all the spreadsheets to be sorted based on the oldest date to the newest date (data from all spreadsheets) then finally at the bottom because some of the dates don?t have a date but instead have "-" I want this at the bottom as it is not relevant. Can you do a formula for me to allow for the request.
**I have attached a sample workbook
Bookmarks