Forum Discussion

Carl_61's avatar
Carl_61
Iron Contributor
Apr 07, 2025
Solved

Formula Help

Hello Everyone, I have attached a sample of my workbook and here is what I am trying to accomplish.  I have a Tab called ALL! (yes I know, will change it some day) that houses names and information ...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Apr 08, 2025

    In sheet Lists in the attached file you can enter your priorities as shown in range C4:C20. The formula in the attached file displays the data ordered according to the values of range C4:C20.

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Apr 09, 2025

If i do this in the sample file the formula dynamically updates the results in the Vacant Rooms sheet. In the ALL sheet i've added column F with some sample data.

 

I intentionally created a #SPILL! error in cell A2 as you can see in the screenshot below (#ÜBERLAUF! in german Excel).

The reason for the #SPILL! error is that there is another formula in cell A3 as you can see in the screenshot below.

The spilled results of these formulas overlap and therefore return a #SPILL! error. You can delete any of these formulas to remove the error.

 

In the attached file i've added the DOR column F in the ALL! sheet.

Carl_61's avatar
Carl_61
Iron Contributor
Apr 10, 2025

So I just realized a sum formula at the bottom of the sheet was causing the #SPILL!. I must have had the formula in the SPILL Range.  Can i add a formula at the bottom after the SPILL Range?

 

Carl

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Apr 10, 2025
    =VSTACK(LET(prioritized,SORT(Lists!A4:C20,3,1,FALSE),
cnt_occ_status,COUNTIFS(ALL!U3:U170,CHOOSECOLS(prioritized,1)),
filtered_occ_status,SORT(FILTER(HSTACK(prioritized,cnt_occ_status),cnt_occ_status),3,1,FALSE),
result,IFNA(DROP(REDUCE("",INDEX(filtered_occ_status,,1),LAMBDA(u,v,
VSTACK(u,"Occ Status: "&v,ALL!A2:U2,HSTACK(FILTER(ALL!A3:U170,ALL!U3:U170=v,"")),
HSTACK(v& " Total:",XLOOKUP(v,INDEX(filtered_occ_status,,1),INDEX(filtered_occ_status,,4))),"",""))),1),""),
IF(result=0,"",result)),
CHAR(SEQUENCE(,COLUMNS(ALL!A2:U2),65)))

    You can VSTACK formulas.

    =VSTACK(Firstformula,

    Secondformula)

    Firstformula is the formula which you already have in cell A2 and Secondformula is CHAR(SEQUENCE(,COLUMNS(ALL!A2:U2),65)) in my example.

Resources