Forum Discussion
Dynamic Spacing of Array Formula
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 f...
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),""))