Cross sheet functionality and maintenance of the chronological order of a formula

---

Excel_Struggler wrote:

The solutions I can think of are:
1. Simplifying the formula making it less stress intensive so that it would be possible to merge all sub-sheets into one big sheet

---

This would be the way to go, in my opinion. Add another column with data validation to select the Week number, rather than having each week on a separate tab.

Regarding your description for "Order of Retrieval", I'm struggling to follow what you meant because your original formula doesn't seem to be doing exactly what you've described (to the best of my understanding). However, since your question doesn't appear to indicate that there's anything wrong with the formula, I made the assumption that it's already returning the desired results.

The following single-cell dynamic array formula returns the same results as your original formula(s), with the additional lookup criteria for week number:

=LET(r, 5, c, 3,
    data, WRAPROWS(TOCOL(D6:F25), r*c),
    array, A6:A25 & B6:B25 & C6:C25,
    keyId, FILTER(array, NOT(MOD(SEQUENCE(ROWS(array),, 0), r))),
    rowId, SEQUENCE(ROWS(keyId)),
    row_nums, MAP(keyId, rowId, LAMBDA(v,n, XMATCH(v, TAKE(keyId, n-1),, -1))),
    WRAPROWS(TOCOL(IFERROR(INDEX(data, row_nums, SEQUENCE(, r*c)), "")), c)
)

Sample Results

Note: I unmerged columns B to F in my sample file, so the Factor data is now referencing range D6:F25 instead of G1:I25.

This method assumes each item always has the same number of records (5 rows each). If the number of records is not consistent, however, a "fill down" approach is needed:

=LET(
    FILLDOWN, LAMBDA(range, SCAN("", range, LAMBDA(a,v, IF(v="", a, v)))),
    keyId, FILLDOWN(A6:A25) & FILLDOWN(B6:B25) & FILLDOWN(C6:C25) & "|" & SCAN(0, A6:A25, LAMBDA(a,v, IF(v="", a+1, 1))),
    rowId, SEQUENCE(ROWS(keyId)),
    row_nums, MAP(keyId, rowId, LAMBDA(v,n, XMATCH(v, TAKE(keyId, n-1),, -1))),
    IFERROR(INDEX(D6:F25, row_nums, {1,2,3}), "")
)

EDIT: you may need to remove the concatenation of column B (week number) from the keyID array. See the v2 file attached, if the first file does not work as expected...