formula to retrieve all values that fall under Row 1 header name

I think you're going to really struggle to do that with formulas. VBA is reasonably trivial but not sure whether that is an acceptable solution.

WBD

If you don't mind to use a lot of helper cells.

Create new sheet for total sheet name listed.
For my sample , in "SheetList" Range D4:E15

Helper cells.
I2
=IF(L1=K1,IF(MAX(I$1:I1)+1<=SheetList!$G$4,MAX(I$1:I1)+1,0),MAX(I$1:I1))

J2
=IF(I2>0,INDEX(SheetList!$D$4:$D$15,I2+1),"")

K2
=IF(I2>0,INDEX(SheetList!$E$4:$E$15,I2+1),0)

L2
=IF(I2>0,IF(J1<>J2,1,L1+1),0)


Output area
A2
=IF($I2>0,IFERROR(INDEX(INDIRECT("'"&$J2&"'!1:1000000"),$L2+1,MATCH(A$1,INDIRECT("'"&$J2&"'!1:1"),0)),""),"")

Regards.