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 first item of spill array.
I need to press Shift-F9 or F9 to show the fill spill array.
This takes too much time.
This happended after the recent update.
USING: EXCEL 365 LATEST VERSION
Excel's recalculation mode might have changed after the update.
Follow these steps to ensure it's set to Automatic:
Go to the Formulas tab.
Click on Calculation Options in the Calculation group.
Ensure that Automatic is selected (not Manual).
If it is set to Manual, change it to Automatic. This will ensure that Excel recalculates formulas and updates spill ranges automatically when changes occur.
My answers are voluntary and without guarantee!
Hope this will help you.
- Thanks Nikolino. The reason I have set it to Manual is because it is a very large file with 150K rows of raw data and automatic calculation will take more than 5 minutes - haven't really tested how long actually.
- 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...
