Hello so i am trying to do dynamic array formula which uses “calendar style” layout
so 1-7, 8-14, 15-21, 22-28, 29-(month max date) and in between i am trying to have data set for every name in that corresponding row.
the layout is something like this
since i have multiple sheets with multiple people filling out i need to import ranges with ID that is given but the problem is i cant seem to make TRANSPOSE function work for every value (so for example i can only apply it to one cell in this case B5, i cant apply to range like “B5:B5+needed amount) and i need to make it for for every ID (+ 3 row for each of “weeks”)

=ARRAYFORMULA(
{
TEXT(DATE(2024, 1, 1) + COLUMN(H:N) - 8, "ddd d");

TRANSPOSE(IMPORTRANGE(VLOOKUP(B5,(IMPORTRANGE(MP!$C$7,"User List!B3:J1000")),9),   "Jan!O7:O13"));

 TEXT(DATE(2024, 1, 8) + COLUMN(H:N) - 8, "ddd d");


 TEXT(DATE(2024, 1, 15) + COLUMN(H:N) - 8, "ddd d");


 TEXT(DATE(2024, 1, 22) + COLUMN(H:N) - 8, "ddd d");

 IF((DATE(2024, 1, 29) + COLUMN(H:N) - 8) <= EOMONTH(DATE(2024, 1, 1), 0), TEXT(DATE(2024, 1,  29) + COLUMN(H:N) - 8, "ddd d"), "")
}
)

Sorry this is my first post so i don’t know if i am explaining the problem well i hope you guys understand.

I’ve tried INDIRECT function, and putting ARRAYFORMULA inside of array formula but it still does not work, I’ve also tries BYROW function but i cant seem to make it work.