In Google spreadsheets, there are pages from 1.WEEK, 2.WEEK..... to 52.WEEK, but some pages are missing, for example, 8.WEEK, 12.WEEK, 19.WEEK are missing randomly; accordingly; I want to bring the B9:G cells of each page, but I want to see the week number first so that I can know which page the incoming data belongs to, so I wrote the text 1ST WEEK OF THE YEAR in the C5 cell of all pages;
What I want is to first print the C5 Cell as many as the full rows in the page and bring the B9:G columns to the other columns.
I can only bring data for 1 page, so I wrote a formula like the one below, but as I mentioned above, how can I do it;
=ARRAYFORMULA(IF(LEN('1.WEEK'!B9:B), {ARRAYFORMULA(ROW('1.WEEK'!B9:B)*0 + '1.WEEK'!C5), '1.WEEK'!B9:G}, ""))
You can add an 'All data' tab and use this formula to merge the data from existing ?.WEEK tabs there:
=let(
stackSheets_, lambda(sn, ra, c, reduce(tocol(æ,2),sn,lambda(rs,s,let(r,indirect(s&"!"&ra),c,indirect(s&"!"&c),l,max(index(row(r)*iferror(r<>""))),d,filter(r,row(r)<=l),r_,lambda(v,byrow(d,lambda(_,v))),if(counta(iferror(d)),vstack(rs,hstack(r_(s),r_(c),d)),rs))))),
sheetNames, sort(sequence(53) & ".WEEK"),
stackSheets_(sheetNames, "B9:G", "C5")
)
In each row, the first column shows the name of the tab where the data comes from, the second columns shows what's in cell C5 in that tab, and the following columns have the data from columns B9:G in that tab. Blank rows in columns B9:G are weeded out.
See the stackSheets_ pattern.