Autoformatting sheet when using multiple formulas that spill?

Question

Hi all-

I'm trying to create an Excel report that is "end-user friendly", including two lists are produced via formulas that 'spill'. Either list could have up to about 20-25 rows, but as little as 1-2 rows. The report isn't elegant when the first list is 2 items, followed by 20 blank rows, followed by another list.

The lists have headers in an adjacent column, but I do need to separate the two lists by one blank row for visual effect.

Are there any easy/ideal approaches to link two formulas that spill to reduce empty space on a report? I'm thinking of complicated ways to count the number of rows in the first formula, then an 'IF' statement in every subsequent row to trigger the second spill formula into the correct row, but that seems very hacky.

Is there maybe a way to combine the two spill lists into one formula (with one blank row between) and just push it to the sheet as one combined Spill?

Answer 1

Without VSTACK (really, don't bother doing it this way (there must be a better way)):

…and thats with the same number of columns, no headers. Yuk.

I suppose it could be made into a lambda function to hide all this nonsense.

Here's that nonsense:

and the lambda is called VertStack and the formula is:

=LAMBDA(FILT1,FILT2,LET(ColmCount,COLUMNS(FILT1),DestnArray,SEQUENCE(ROWS(FILT1)+ROWS(FILT2)+1,ColmCount),colms,MOD(DestnArray-1,ColmCount)+1,rws,INT((DestnArray-1)/ColmCount)+1,IFERROR( IF(DestnArray<=((ROWS(FILT1)+1)*ColmCount), INDEX(FILT1,rws,colms), INDEX(FILT2,rws-ROWS(FILT1)-1,colms)),"")))

Only 2 tables and the same number of columns in both filters. I think I've worked this one to death.

Answer 2

I'm struggling to do this without VSTACK easily and still looking, in the meantime I was playing with these newish functions and managed to get a couple of filtered tables, even with differing numbers of columns to display quite nicely using IFERROR to hide all the errors!:

Answer 3

I do have SEQUENCE, but current version is not showing TOCOL or VSTACK - I'll check for available updates at my next opportunity.

Answer 4

If I have understood you correctly, then yes it is possible. How easy it is will depend on which functions you have available to you. Do you have new functions like VSTACK or TOCOL? Do you have SEQUENCE?

Posting a sample workbook would also help us to help you - showing an example of what you have and what you want as output.

Answer 5

I'm trying to create an Excel report that is "end-user friendly", including two lists are produced via formulas that 'spill'. Either list could have up to about 20-25 rows, but as little as 1-2 rows. The report isn't elegant when the first list is 2 items, followed by 20 blank rows, followed by another list.

The lists have headers in an adjacent column, but I do need to separate the two lists by one blank row for visual effect.

You can not stack up to spill formulas.

Write the first formula in A1 and the second formula in A101, so you can be sure there is enough place for the formulas to spill.

In A100 apply the formula =CHAR(160)

Select the rows 1:200 (to be sure you have all possible rows) and apply an Autofilter and filter out the blank rows.

Save the file.

The next time you want to print a report simply reapply the filter, done.

Andreas.