VSTACK, INDIRECT, and BYROW to pull in data from many sheets referencing a range of cell addresses

Occasional Contributor

(update - attached file)


Hi all! 


I have a unique problem which is a result of having to create a large "output" range from multiple sheets. I'm building a large "demo data" bank, but the result needs to be one big chunk of data. 


Here's how I have it set up: there are many many sheets that contain the same structure of data, but each sheet is different. The columns are also all the same. So in each sheet, there are 9 columns of data, and each column is a dynamic array. For example, in one sheet I may have the following data: 





and in another sheet I may have this data: 




The beauty of how I set it up is that all of the columns have the same exact address. So both in Sheet1 and Sheet2, the addresses of the "column_#" are for example: 


Because all of the columns of data are dynamic arrays, I can use an indirect reference to pull in the data. So the correct reference of the columns is simply the next row + the "#"

reference to datacolumn_1column_2column_3column_4

In my big output sheet, I combine all of the columns together using VSTACK and INDIRECT. So (I added a column with the row numbers and a row with column numbers)

4SalesDE1/3/22 324.11



This setup is nice because It gives me one nice range that I can then copy into a csv file as "raw data" for sequential processing, but it allows me to manipulate the logic in each sheet as needed. For clarification, the results in each column's rows 3:6 are also one array. 


However - there's a big problem. I have over 50 sheets of data. In the formula with the VSTACK above, I have to manually specify each cell with the INDIRECT reference. This is both tedious and disappointing because I can generate the text needed for the indirect reference automatically (simply by listing all sheets, generating the name of the sheets from the sheet number with a tiny bit of VBA, and creating a sequence of addresses with a SEQUENCE since they are all always in the same row and the same column. 


My question is - given that the references that lie in A1:C2 is actually one big dynamic array, how does one generate the correct BYROW function to pull those references? 


I've tried (repeating this in each "row 3" of the output sheet) =BYROW(A1:A2,LAMBDA(x,VSTACK(INDIRECT(x))))) - but I inherently new that just wouldn't work - and it sure doesn't. 

12 Replies



I would imagine there's a much better set-up without all this volatility. Since you're in any case already using some VBA, why not simply add a procedure to perform your required consolidation?


That said, here's one possibility:




A1:A2 can be amended as required. And this can of course then be copied to the right.

@JosWoolley - thanks! That didn't quite work for me. I made an example sheet to illustrate. Would greatly appreciate the help! 



Can you explain how it "didn't work" for you? I can't see any attempt in that workbook of you trying to implement my solution. I replaced A1:A2 with I6:I8 in the formula I provided and it gave the exact same results as yours in I13:I42.

Apologies. Before trying again - is the "zeta" symbol in your formula above just a placeholder for any letter? Thanks!

You can rename it to whatever you like if you wish.


VSTACK accepts 3D references. You could use:

Hi, thanks again! Interestingly enough, it does not want to work if there's a lot of rows and a lot of references. I'm trying to figure out a way to get you a demo worksheet given the sensitivity.
Thanks, but the issue is that there are different amounts of data in each sheet - so "E13" might be "E3000". I guess I could filter it thought?
Yes, stack and filter to clean up the stack. I recommend avoiding INDIRECT, if possible, because of its volatility.

if possible,try this online tool



select rowid,文件名,表格名,date,divison,amount,account from consolidate_all_sheets where rowid>6 and date!='date'