Calculating (12 threads) problem

[danhendo888]

https://imgur.com/a/0AgdX6M

This wasn't a problem but sometimes when I hit SHIFT + F9, my workbook freezes up, the fans on my computer start spinning fast and nothing really happens.

This sheet for example, only has one row of formulas. The sheet of data that it is referencing only has 200 rows.
Sometimes it doesn't happen, and then other times I get the error.

Any idea what might be the cause of this?

EDIT: Just tried SHIFT + F9 and it closed the workbook. Reopened and tried again, and the formulas refreshed no problem. Very odd.

 

[Michael M]

you don't say what the formula is /
AND
what happens if you press CTRL + End....where does the cursor go to ?

 

[danhendo888]

Hi Michael,


CTRL + END takes me to row 99

When I refresh the active sheet, it freezes more often than not.
Sometimes it freezes up, sometimes it does not. This wasn't a problem until I started putting macros in.


Formulas are:

Book1
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	PO 1	PO 1 Amount	PO 1 Currency	PO 1 Booked	PO 1 Paid	PO 1 Match	GFF	GSS List	PO 2	PO 2 Amount	PO 2 Currency	PO 2 Booked	PO 2 Paid	PO 2 Match	GFF PO
2		-						No		0
APL Co.

Cell Formulas
Range	Formula
H2	=SUMIFS(Sheet2!N:N,Sheet2!L:L,G2)+SUMIFS(Sheet2!O:O,Sheet2!L:L,G2)
J2	=IFNA(VLOOKUP(G2,Sheet2!L:M,2,FALSE),"")
N2	=IFNA(IFERROR(IF(MATCH(B2,'GSS List P07'!J:J,0)>0,"Yes","No"),"No")&" "&TEXT(VLOOKUP(B2,'GSS List P07'!J:K,2,FALSE),"DD/MM/YYYY"),"No")
P2	=SUMIFS(Sheet2!N:N,Sheet2!L:L,O2)+SUMIFS(Sheet2!O:O,Sheet2!L:L,O2)
R2	=IFNA(VLOOKUP(O2,Sheet2!L:M,2,FALSE),"")
T2	=IFNA(VLOOKUP(O2,Sheet2!L:CW,MATCH(Table1[[#Headers],[FI Document Number]],Table1[[#Headers],[PO Number]:[FI Document Number]],0),0),"")
G2	{=IFERROR(INDEX(Table1[PO Number],SMALL(IF(FREQUENCY(IF((Table1[Vendor]=$A2)*(Table1[PO Header Text]=B2),MATCH(Table1[PO Number],Table1[PO Number],0)),ROW(Table1[PO Number])-ROW(Sheet2!$L$1)),ROW(Table1[PO Number])-ROW(Sheet2!$L$1)),COLUMNS($G2:G2))),"")}
I2	{=IFERROR(INDEX(Sheet2!L:AN,MATCH(G2,Sheet2!L:L,0),MATCH("PO Currency",Sheet2!$L$1:$AN$1,0)),"")}
K2	{=IFERROR(IF((LARGE(IF(Table1[PO Number]=G2,Table1[Payment Date]),1))=0,"",LARGE(IF(Table1[PO Number]=G2,Table1[Payment Date]),1)),"")}
L2	{=IFNA(VLOOKUP(G2,Sheet2!L:CW,MATCH(Table1[[#Headers],[FI Document Number]],Table1[[#Headers],[PO Number]:[FI Document Number]],0),0),"")}
M2	{=IFNA(LEFT(VLOOKUP(G2,Sheet2!L:X,MATCH(Table1[[#Headers],[Legacy PO Number]],Table1[[#Headers],[PO Number]:[Legacy PO Number]],0),0),10),"")}
O2	{=IFERROR(INDEX(Table1[PO Number],SMALL(IF(FREQUENCY(IF((Table1[Vendor]=$A2)*(Table1[PO Header Text]=B2),MATCH(Table1[PO Number],Table1[PO Number],0)),ROW(Table1[PO Number])-ROW(Sheet2!$L$1)),ROW(Table1[PO Number])-ROW(Sheet2!$L$1)),COLUMNS($G2:H2))),"")}
Q2	{=IFERROR(INDEX(Sheet2!L:AN,MATCH(O2,Sheet2!L:L,0),MATCH("PO Currency",Sheet2!$L$1:$AN$1,0)),"")}
S2	{=IFERROR(IF((LARGE(IF(Table1[PO Number]=O2,Table1[Payment Date]),1))=0,"",LARGE(IF(Table1[PO Number]=O2,Table1[Payment Date]),1)),"")}
U2	{=IFNA(LEFT(VLOOKUP(O2,Sheet2!L:X,MATCH(Table1[[#Headers],[Legacy PO Number]],Table1[[#Headers],[PO Number]:[Legacy PO Number]],0),0),10),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
Currency	='File Name'
CY	=#REF!#REF!
Date	=#REF!#REF!
List	=#REF!#REF!

 

[Michael M]

what are the macros that are causing the issues ??
I'd also suggest changing your formulas from referencing entire columns to a more useable range
formuals like this should be something like

{=IFERROR(INDEX(Sheet2!L1:AN1000,MATCH(G2,Sheet2!L1:L1000,0),MATCH("PO Currency",Sheet2!$L$1:$AN$1,0)),"")}

 

[danhendo888]

Sub APL()
'
' APL Macro
'
Workbooks.Open "C:\Users\MSI\Downloads\Delete 5.XLSX"
'
    Range("A1").Select
    Windows("Delete 5.XLSX").Activate
    Range("A1").Select
    Windows("02.08.2019 Master.xlsm").Activate
    Windows("Delete 5.XLSX").Activate
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveCell.Range("A1:EM24953").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Workbooks("02.08.2019 Master.xlsm").Sheets("APL Co.").Range( _
        "A1:A2"), CopyToRange:=ActiveCell.Offset(24954, 0).Range("A1"), Unique:= _
        False
    Windows("02.08.2019 Master.xlsm").Activate
    Windows("Delete 5.XLSX").Activate
    Range("A1").Select
    Windows("02.08.2019 Master.xlsm").Activate
    Windows("Delete 5.XLSX").Activate
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Windows("02.08.2019 Master.xlsm").Activate
    Sheets("Sheet2").Select
    Range("Table1[GL Account]").Select
    ActiveSheet.Paste
    Range("A2").Select
    Sheets("APL Co.").Select
    Range("A1").Select
    ActiveSheet.Calculate
    Application.DisplayAlerts = False
    Workbooks("Delete 5.XLSX").Close
    
End Sub

I recorded steps of me opening another workbook, filtering the data in that workbook, then copying/pasting that data onto my first workbook.
Then I would hit SHIFT+F9 on my sheet of formulas to populate those cells.
Since the filtered data comes in different row sizes, I formatted as a table the destination of where I paste the data. Thus the referencing of entire columns.

 

[Michael M]

But you must have some idea of the gerresal range of data ?
1000 rows, 10000 rows ?you could then limit the number of rows in the formula.
you could also apply the formula via the macro thus being able to get the exact number of rows each time !

 

[danhendo888]

But you must have some idea of the gerresal range of data ?
1000 rows, 10000 rows ?you could then limit the number of rows in the formula.
you could also apply the formula via the macro thus being able to get the exact number of rows each time !

Hi Michael,
I suppose I could use the COUNTA function. Actually, I didn't quite think of that until now.

Also, with regard to my Excel freezing up every now and then, I think it was a computer issue.
On my home laptop, Excel will keep crashing.
On my work laptop, no problems.

Thank you for your input, I appreciate it.