Forum Discussion
Re: Formula Help
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.
4 Replies
Hello Oliver, first and foremost I want to thank you for your help with this formula. I personally would not have ever been able to come up with the formula that made this happen. I was wondering, because I have no idea what exactly this formula is doing, if you could assist me with one more formula very similar but somewhat different. Using the same data, but different cells, I need to do the same thing but create groups based on POSITION [POS] first and then sort those results based on the DOR. What is obvious here is the DOR date needs to be sorted in descending order. The thing about POSITION [POS] (which is what the groups are based on) is that the column on the ALL sheet shows an Acronym such as V1, V2, FC and so on but the Rate of the POSITION (defined on the Lists! sheet) is matched to the Rate on the Lists! sheet alongside the POSITION, which is Data used to sort based on the Rate. The result is to have all records on the ALL! Sheet that equates to 6, Group with all other 6 Ratings and within the group sorted in descending order based on DOR (Date of Rate), then spaced out just like the other formula, the next group will now equate to the 5, Group with all 5 Ratings and sorted in descending order by DOR and so on. The POSITIONS & RATES table can be found on the Lists! Sheet in cells M23:O60. The formula I need for this is to be placed on the 1010 BAH Wait List! in cell A2. Different Sections have different Positions and, in some cases, the same, so when it comes to the RATE Group, Different Positions will fall into the same Rate Group. So, I think the Position and the Rate has to be checked against the Section. You can also see in the POSITIONS & RATES table (Not a table though), that some Positions, within the Section, carry the same Rate.
I have provided minimum sample data in the attachment.
I hope I did not confuse you with this explanation.
Thank you,
Carl
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
=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.
Hello Sir,
Question? Yesterday my sheet worked super fine. Today, I'm getting #SPILL! in cell A2. I don't recall doing anything that would have caused this. The only thing I did was changed the Sheet name to "Occ Status By Status Code". Did this do something that needs changing somewhere else? I also added the DOR column for which I understand you accounted for this in your formula.
Would you please assist me in getting this working again.
Thank you,
Otherwise, this solution is working great.
Carl
