I have a sheet with various tabs. Each tab has the same column header in A1 and similar data (same format) in column A. In "sheet2" I have a list of all the tab names in Column A. I wish to combine all the data for all tab's row A into "sheet2" in column B.

I have managed to get the desired result using... =ARRAYFORMULA({indirect(A1&"!A2:A"&COUNTA(indirect(A1&"!A:A")));indirect(A2&"!A2:A"&COUNTA(indirect(A2&"!A:A")))})

However, this only covers 2 tabs, and I have about 50 tabs to add, with more tabs added regularly.

So I wish to try and use a QUERY formula, which was recommended to me. I have run a few versions, see below, but the resulting output is 5 rows of

#VALUE!

BYROW and LAMBDA are new to me and I am uncertain on what I am doing wrong.

=QUERY(BYROW(A1:A5,LAMBDA(x, INDIRECT(x&"!A2:A6")))))

=ARRAYFORMULA({QUERY(BYROW(A1:A5,LAMBDA(x, INDIRECT(x&"!A2:A6"))))})

Below is 2 samples of the tabs where the data is stored, each tab is names after the Product Type, so EE and EX in this sample, and those same tab names are used in the third table (below).

This is my "sheet2"

This is the sample sheet I am working on, columns F, G and H contain my recent attempt, it is Column H which I wish to get working. https://docs.google.com/spreadsheets/d/1gvOZ_GuZx6HOLHQ7WwyIlFHrAihoNXz2ysZkublh7Fs/edit#gid=1077440502

Expected Outcome table

CodePudding user response：

Unlike BYROW, REDUCE supports nested arrays. You can use it to loop through A2:A6 of Sheet2 and add arrays from other sheets using array literals {;}

=REDUCE("Product Code", A2:A6, LAMBDA(a,c,{a;indirect(c&"!A2:A"&COUNTA(indirect(c&"!A:A")))}))