Forum Discussion
SPILL RECALC
Hi, I have a large excel file using Byrow with unique, filter & sumifs functions to extract data. Before, I just press F2 and Enter to return the spill array. Recently, It does now show the fir...
What does your formula look like?
SPILL FORMULA
=BYROW(SEQUENCE(ROWS(AGENTLIST)),LAMBDA(xROw,
LET(
cYEAR,2024,
cNew,"NEW",
cSM,INDEX(AGENTLIST,xROw,2),
cSS,INDEX(AGENTLIST,xROw,3),
cPOS,INDEX(AGENTLIST,xROw,4),
cCustomer,UNIQUE(FILTER(SALES_TABLE[CUSTOMER],1
*(IF(cSM=" ",1,SALES_TABLE[SM]=cSM))
*(IF(cSS=" ",1,SALES_TABLE[SS]=cSS))
*(SALES_TABLE[CATEGORY]<>"RAWMAT")
*(SALES_TABLE[CATEGORY]<>"MATERIAL")
*(SALES_TABLE[INVYEAR]=cYEAR)
*(IF(cNew="ALL",1,SALES_TABLE[NEW]=cNew)),"EMPTY")),
IF(INDEX(cCustomer,1,1)="EMPTY",0,ROWS(cCustomer)))))
The AGENTLIST is a spill array of agent names, team and manager.
The SALES_TABLE is Excel Table of Raw Sales Data (150,000+ rows)
I hope this helps...