2D spilled array, using REDUCE and INDIRECT to consolidate data from multiple sheets.

[PrettyGood_Not Great]

Hi, I have an equation that is indirectly pulling a row of data from multiple sheets, all having an identical structure.

REDUCE(0,C11,LAMBDA(a,b,INDIRECT("'"&b&"'!H$13:AU$13")))

This equation is being used on a sheet that is consolidating all other sheets into one data set. The equation spills to the right, however I'd like it to also spill down. C11 in this equation is the reference to a column that contains all the tab names. I have tried turning C11 into C11# as it is already a dynamic range it's self, however that returns a #REF.

I have also tried using VSTACK('Tab1:Tabx'!H$13:AU$13) and this works perfectly spilling in 2D, however I have to hard code the tab names and this is not acceptable. Is there a way to either A) turn the REDUCE/INDIRECT into a 2D spilled array or B) allow the VSTACK to reference cell values containing the tab names?

 

[Fluff]

Why not add 2 sheets one called Start before the sheets your interested in & 1 called End after your sheets & you can then use

=drop(drop(VSTACK('Start:End'!H$13:AU$13),-1),1)

 

[PrettyGood_Not Great]

Why not add 2 sheets one called Start before the sheets your interested in & 1 called End after your sheets & you can then use

=drop(drop(VSTACK('Start:End'!H$13:AU$13),-1),1)

Hi Fluff, that could be done yes, but not ideal as this workbook is meant to be picked up by other users as a template and having an equation dependent upon their choice of tab creation will lead to issues. Additionally, if the user is unaware they may move the sheets around.

 

[Fluff]

What is the formula in C11?

 

[PrettyGood_Not Great]

What is the formula in C11?

There is no formula, it is a named range that is pulling a list of the tab names from another sheet. If it were not a named range it would simply be a data pull using the = operator only.

 

[Fluff]

C11 is not a valid name, so it cannot be a named range.