Dynamic List From Multiple Worksheets (with same Format) and Based on Date Ranges

Hi All -

I have spent about 48 hours trying to solve this one now, and only reached 50% of the solution...

I have an HR spreadsheet for staff (25 staff in reality across 25 worksheets, all sheets with the same layout/format/cell references).

Every week I have to provide absence reports and I am looking for a way of pulling through all results live/dynamically on a front sheet, based on dates inputted at the top of the summary sheet matching those in the individual records.

I have sorted the dates issue, this works OK, but I can only get it to pull through from one sheet referenced in the formula. I suppose what I am trying to achieve is '3D' referencing here, and I can't work it with this formula for some reason (there may be a more eloquent way of doing this too, though I'm not sure). Ideally, I'd also like one of the columns to show the name of the tab it came from (i.e. staff name) via formula.

I've attached an e.g. of how far I got with just two sample sheets to work from. Obviously working across 25 sheets in the end, I am looking to keep formulas as neat as possible so the sheet doesn't get too slow. I am also trying to avoid VBA if possible.

Any help would be greatly appreciated

Thanks,

Eduard-Stoo

The simple (correct!) answer is to have ALL data on one sheet with a Employee name column: having multiple sheets is not a good approach and will possibly require a VBA solution OR very complex formulae.

Created sheet "Absence"

in "Main Summary" ..

in A5

=IFERROR(INDEX(Absence!$L$5:$L$62,SMALL(IF((Absence!$C$5:$C$62>=$A$2)*(Absence!$C$5:$C$62<=$B$2),ROW('S Featherton'!$B$5:$B$62)-ROW(Absence!$B$5)+1),ROWS(A$5:$A5))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Then copy down

Similar for other columns

I not you do not enter "To" data as a norm so the test above for date in open to error as it only checks column C.

=IFERROR(INDEX(Absence!$L$5:$L$62,SMALL(IF((Absence!$C$5:$C$62>=$A$2)*(Absence!$F$5:$F$62<=$B$2),ROW('S Featherton'!$B$5:$B$62)-ROW(Absence!$B$5)+1),ROWS(A$5:$A5))),"")

For "Day of Week": set to date in C/F and format as "ddd"