I have a summary sheet and 5 detail sheets (additional details sheets will be added). Each detail sheet has information in cells A10:G16. I want to join those ranges from each detail sheet in a single list on the summary sheet. Right now I have the following formula in Summary sheet!b1

={DetailSheet1!A10:G16;DetailSheet2!A10:G16;DetailSheet3!A10:G16;DetailSheet4!A10:G16;DetailSheet5!A10:G16}

Which works. However, as my list of detail sheets grows, I don't want to have to go back and add another segment to the formula. I would like to just have a list of the names of each detail sheet and have the formula pull the A10:G16 range from each detail sheet in the list.

CodePudding user response：

Do you want to add other future new sheets without modifying your formula?

It's not ideal to do it this way because it will be slow, but considering it is a small range and imagining your spreadsheet is not that wide, you could use this formula:

=MAKEARRAY(7*COUNTA(A:A),6,LAMBDA(r,c, INDEX (INDIRECT(INDEX(A:A,rounddown(r/7,0) 1)&"!A10:G16"),IF(MOD(r,7),MOD(r,7),7),c)))

Where you have in A:A the list of names of your sheets. Obviously you can change the range