Dynamic Spacing of Array Formula

[adamclang]

Hi all,

I have a simple dashboard designed in excel which provides a summary of data in a worksheet. It's for quick reference and printing.

It uses four array formula combining the sort and filter functions to provide smaller tables of data. It works currently as I'd Iike apart from an annoying layout issue.

Depending on options selected in a drop down list, the results for each formula will be a table of varying rows. If the tables are too close to each other I'll get a Spill error if they overlap, but when the result tables are shorter I'll have ugly looking gaps between the tables.

Is their a way two keep a dynamic spacing, say two rows, between the returning tables?

I think it probably involves combining the formula, but each of the formula outputs different columns from the main dataset, and so they need their own set of column headers.

Any ideas?

Thanks,

Adam

[#2]

@adamclang 

You can use the VSTACK function to "stack" the result of multiple formulas below each other.

[#3]

@adamclang 

It depends on the positioning of your dynamic arrays, but in the example pictures, I have three spilled arrays in B3, G3 and B16. 

Assuming those would grow in either direction, I would need a better way of positioning them together. So, suppose they aren't actually individually on the same sheet, but rather in separate locations where they can grow as needed. 

Then I can use something like this to join them together. 

The important concept here is to use EXPAND to create blank rows or columns which are as wide as the widest of two arrays that are on top of or next to each other. 

=LET(first,B3#,second,G3#,third,B16#,
pane1,VSTACK(first,EXPAND({""},2,MAX(COLUMNS(first),COLUMNS(third))),third),
pane2,HSTACK(EXPAND({""},MAX(ROWS(pane1),ROWS(second))),second),
IFERROR(HSTACK(pane1,pane2),""))