Home > Software design >  How do I make a dynamic query?
How do I make a dynamic query?

Time:12-14

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

  • Related