Complicated Cell Formula to VBA Formula

[LHarting]

Explanation: There are 4 Sub-inventories an item can be found in*. Staged to load (STG), International (INTNL/FGINT), an over-flow warehouse (PARG), or in the racks (FG).
An Sales orders are labeled International (Open Orders 1 sheet Column E), and the the Status of the order can be found in one of two reports (Columns I and J).
*There are more, but I'm just concerned about 4.

Here's where it gets complicated. An order's status can be Staged AND International (Both), and FG/Parg ITEMS can help cover both International and Staged ORDERS, but Staged Items can ONLY fill orders that are Staged until all Staged Orders are filled - then they can only fill orders that are International, and International Items cannot cover Orders that aren't International (or both).

International - International
Staged - Staged until a) The item is used up or b) there are no more Staged orders and now can be used for International.
Parg/FG - International, Staged, Neither.

The question is - What do we have in-stock?

This is my formula. There HAS to be a way to do this in VBA that's either easier or at least far more clean. HELP?

=IF(SUM(IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0),IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,VALUE($C2),INV!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0))=0,0,IFERROR(IFS(AND($K2="Both",IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"STG","FGINT"})),0)>0),IFERROR(IFS(OR(COUNTIF($C$2:$C$6500,$C2)=1,COUNTIFS($C$2:$C2,$C2,$K$2:$K2,"Both")=1),IF(IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG"})),0)>='Open Orders1'!$D2,IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG"})),0),IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0)),COUNTIFS($C$2:$C2,$C2,$K$2:$K2,"Both")>1,IF(IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG"})),0)>=$D2,MINIFS($G$1:$G1,$C$1:$C2,$C2,$K$1:$K2,"Both"),SUM(IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0),-SUMIFS($D$1:$D1,$C$1:$C1,$C2)))),0),AND($K2="INTNL",SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,"FGINT")>0),IFERROR(IFS(OR(COUNTIF($C$2:$C$6500,$C2)=1,COUNTIFS($C$2:$C2,$C2,$K$2:$K2,"INTNL")=1),IF(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,"FGINT")>='Open Orders1'!$D2,SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,"FGINT"),IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","PARG","FG"})),0)),AND(COUNTIFS($C$2:$C2,$C2,$K$2:$K2,"INTNL")>1,SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,"FGINT"),-IFERROR(SUMIFS($D$1:$D8037,$C$1:$C1,$C2,$K$1:$K1,"Both"),0))>=$D2),MINIFS($G$1:$G1,$C$1:$C1,$C2,$K$1:$K1,"INTNL")),0),AND($K2="Staged",SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,"STG"),-SUMIFS($D$1:$D1,$C$1:$C1,$C2))>0),IFERROR(IFS(OR(COUNTIF($C$2:$C$6500,$C2)=1,COUNTIFS($C$2:$C2,$C2,$K$2:$K2,"Staged")=1),IF(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,"STG")>='Open Orders1'!$D2,IFERROR(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,"STG"),0),IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"STG","PARG","FG"})),0)),COUNTIFS($C$2:$C2,$C2,$K$2:$K2,"Staged")>1,IFERROR(IFS(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,"STG")>=$D2,MINIFS($G$1:$G1,$C$1:$C1,$C2),SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,"STG"),-SUMIFS($D$1:$D1,$C$1:$C1,$C2))<$D2,SUM(IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0),-SUMIFS($D$1:$D1,$C$1:$C1,$C2))),0)),0),$K2=0,IF(IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0)=IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"PARG","FG"})),0),IFERROR(IFS(OR(COUNTIF($C$2:$C$6500,$C2)=1,COUNTIFS($C$2:$C2,$C2,$K$2:$K2,0)=1),IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"PARG","FG"})),0),COUNTIFS($C$2:$C2,$C2,$K$2:$K2,0)>1,IF(IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"PARG","FG"})),0)>=$D2,MINIFS($G$1:$G1,$C$1:$C1,$C2,$K$1:$K1,0),SUM(IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0),-SUMIFS($D$1:$D1,$C$1:$C1,$C2)))),0),IFERROR(IFS(COUNTIF($C$2:$C2,$C2)=1,SUM(IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0),-SUMIFS($D$1:$D1,$C$1:$C1,$C2)),AND(COUNTIF($C$2:$C2,$C2)>1,SUM(IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0),-SUMIFS($D$1:$D1,$C$1:$C1,$C2))>0),SUM(IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0),-SUMIFS($D$1:$D1,$C$1:$C1,$C2))),0))),IFERROR(IFS(AND(OR(AND($K2="INTNL",SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,"FGINT")<=$D2),AND($K2="Staged",SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,"STG")<=$D2),AND($K2="Both",IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG"})),0)<=$D2),OR(COUNTIF($C$2:$C$6500,$C2)=1,COUNTIFS($C$2:$C2,$C2)=1))),SUM(IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"PARG","FG"})),0),-SUMIFS($D$1:$D1,$C$1:$C1,$C2)),AND(OR(AND($K2="INTNL",SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,"FGINT")<=$D2),AND($K2="Staged",SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,"STG"),-SUMIFS($D$1:$D1,$C$1:$C1,$C2))<=$D2),AND($K2="Both",IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG"})),0)<=$D2)),COUNTIFS($C$2:$C2,$C2)>1),IF(SUM(COUNTIFS($C$1:$C1,$C2,$K$1:$K1,{"Staged","Both","INTNL"}))>0,SUM(IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0),-SUMIFS($D$1:$D1,$C$1:$C1,$C2)),MINIFS($G$1:$G1,$C$1:$C1,$C2))),"x")))

Better just to link to an example:

Example.xlsx

drive.google.com

 

[jasonb75]

If this is the first occurrence of this item or this item only appears once
IFERROR(IFS(OR(COUNTIF($C$2:$C$6000,$C2)=1,COUNTIFS($C$2:$C2,$C2,$K$2:$K2,"Both")=1),

In addition to the points in my earlier post above, is this part correct?

Obviously, you will know the requirement far better than I will, but the absence of the status in the first part looks wrong to me.

 

[LHarting]

I've just been going over your breakdown of the formula again and noticed this part under the sections INTNL - First

There is also a similar anomaly under the Staged - First section, but with FGINT missing from the formula instead of STG

Should it be all 4 sub-inventories as the description says, or just the 3 used in that part of the formula?

Just those three. If the order is International, then I want to pull International and regular inventory, Not inventory that may be Staged for a different Order.
Same but reversed with Staged. I want to pull Staged and regular inventory, but not inventory specific to International.

 

[LHarting]

In addition to the points in my earlier post above, is this part correct?

Obviously, you will know the requirement far better than I will, but the absence of the status in the first part looks wrong to me.

What I'm looking for here is if it's the only time the Item appears on the report, or if it's the first time the item is labeled as "Both."

 

[jasonb75]

This is what I've come up with using formulas and named ranges, it is not instant, but significantly faster than the original method. I have used a few more helper columns than I suggested originally, but these are a necessary evil to improve the performance.

I've made an assumption that "YARD" is the fifth and final possible status in the SUBINVENTORY_CODE column (no empty cells), if this is not the case then it will need another slight change which will slow it down a little.

In summary, the 'All' column calculates the non 'Yard' inventory, which I've assumed will be the same as SUM{FGINT,STG,PARG,FG}. By doing it this way, there is only one sumifs formula to calculate per row where there is a zero inventory instead of 4 or more. With the example file, this reduces the number of sumifs formulas to calculate from ~22000 to ~17000, which is quite a difference.

Several of the helper columns use simple logical tests to eliminate unnecessary complex calculations as much as possible, as does the main formula in column F. I've cut it down as much as possible without compromising the functionality, the sumifs and countifs functions that I've left in the main formula are only used once each so splitting them into helper columns would actually make them less efficient. The others are all used multiple times, so by using the helper columns they only need to be calculated once each.

I did have more named ranges originally, but I've reduced that for simplicity and only used them where the number of rows needs to be variable.

There are 2 still 2 ranges not showing in the list, those are:-

INV_LR which refers to =MATCH(1E+100,INV!$C:$C)
ORD_LR which refers to =MATCH(1E+100,'Open Orders1'!$B:$B)

These are both use in other named ranges, but not in the actual sheet so the XL2BB add-in does not pick them up. @smozgur tagged for reference.

Hopefully this is of some use

Latest.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Schedule Ship Date	Item Number	Ordered Quantity	INTNL	On Hand	Bal After Order	FGINT	STG	PARG	FG	All	Only	First stat	First	Min Stat	Min	Prev Ord		Report 1 Update Status	Report 2 Status	Status
2	17/06/2020	492633-169	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0				0
3	06/10/2020	NPT	4	0	0	0	0	0	0	0	0	0	0	0	0	0	0				0
4	12/10/2020	453634-000	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0			Backordered	0
5	26/10/2020	T063DEW2364013H	24	0	336	312	0	336	0	1336	1672	6	1	1	0	0	0			Released to Warehouse	Staged
Open Orders1

Cell Formulas
Range	Formula
F2:F5	F2	=IF(L2=0,0,IF(IF(V2="Both",(H2+I2)>0),IF(OR(M2:N2=1),IF((H2+I2)>=D2,H2+I2,L2),IF(N2>1,IF((H2+I2)>=D2,P2,L2-R2),0)),IF(IF(V2="INTNL",H2>0),IF(OR(M2:N2=1),IF(H2>=D2,H2,SUM(H2,J2,K2)),IF(IF(N2>1,(H2-SUMIFS($D$1:$D1,$C$1:$C1,C2,$V$1:$V1,"Both"))>=D2),P2,0)),IF(IF(V2="Staged",(I2-R2)>0),IF(OR(M2:N2=1),IF(I2>=D2,I2,SUM(I2:K2)),IF(N2>1,IF(I2>=D2,Q2,IF((I2-R2)<D2,L2-R2,0)),0)),IF(V2=0,IF(L2=(J2+K2),IF(OR(M2:N2=1),J2+K2,IF(N2>1,IF((J2+K2)>=D2,P2,L2-R2),0)),IF(O2=1,L2-R2,IF(IF(O2>1,(L2-R2)>0),L2-R2,0))),IF(OR(IF(V2="INTNL",H2<=D2),IF(V2="Staged",I2<=D2),IF(V2="Both",(H2+I2)<=D2),OR(M2=1,O2=1)),J2+K2-R2,IF(IF(O2>1,OR(IF(V2="INTNL",H2<=D2),IF(V2="Staged",(I2-R2)<=D2),IF(V2="Both",(H2+I2)<=D2))),IF(SUM(COUNTIFS($C$1:$C1,C2,$V$1:$V1,{"Staged","Both","INTNL"}))>0,L2-R2,Q2),"x")))))))
G2:G5	G2	=IF(F2<=0,0,F2-D2)
H2:H5	H2	=IF(L2=0,0,SUMIFS(INVqty,INVItem,C2,INVsub,"FGINT"))
I2:I5	I2	=IF(L2=0,0,SUMIFS(INVqty,INVItem,C2,INVsub,"STG"))
J2:J5	J2	=IF(L2=0,0,SUMIFS(INVqty,INVItem,C2,INVsub,"PARG"))
K2:K5	K2	=IF(L2=0,0,L2-SUM(H2:J2))
L2:L5	L2	=SUMIFS(INVqty,INVItem,C2,INVsub,"<>YARD")
M2:M5	M2	=IF(L2=0,0,IF(O2>1,O2,COUNTIFS(ItemAll,C2)))
N2:N5	N2	=IF(L2=0,0,COUNTIFS($C$2:$C2,C2,$V$2:$V2,V2))
O2:O5	O2	=IF(L2=0,0,IF(N2>1,N2,COUNTIFS($C$2:$C2,C2)))
P2:P5	P2	=IF(OR(N2:O2>1),MINIFS($G$1:$G1,$C$1:$C1,C2,$V$1:$V1,V2),0)
Q2:Q5	Q2	=IF(OR(N2:O2>1),MINIFS($G$1:$G1,$C$1:$C1,C2),0)
R2:R5	R2	=IF(OR(N2:O2>1),SUMIFS($D$1:$D1,$C$1:$C1,C2),0)
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
INVItem	=INV!$A$2:INDEX(INV!$A:$A,INV_LR)	L2:L5, H2:J5
INVqty	=INV!$C$2:INDEX(INV!$C:$C,INV_LR)	L2:L5, H2:J5
INVsub	=INV!$B$2:INDEX(INV!$B:$B,INV_LR)	L2:L5, H2:J5
ItemAll	='Open Orders1'!$C$2:INDEX('Open Orders1'!$C:$C,ORD_LR)	L2, H2:J2, F2:F5, M2:R5

 

[LHarting]

This is what I've come up with using formulas and named ranges, it is not instant, but significantly faster than the original method. I have used a few more helper columns than I suggested originally, but these are a necessary evil to improve the performance.

I've made an assumption that "YARD" is the fifth and final possible status in the SUBINVENTORY_CODE column (no empty cells), if this is not the case then it will need another slight change which will slow it down a little.

In summary, the 'All' column calculates the non 'Yard' inventory, which I've assumed will be the same as SUM{FGINT,STG,PARG,FG}. By doing it this way, there is only one sumifs formula to calculate per row where there is a zero inventory instead of 4 or more. With the example file, this reduces the number of sumifs formulas to calculate from ~22000 to ~17000, which is quite a difference.

Several of the helper columns use simple logical tests to eliminate unnecessary complex calculations as much as possible, as does the main formula in column F. I've cut it down as much as possible without compromising the functionality, the sumifs and countifs functions that I've left in the main formula are only used once each so splitting them into helper columns would actually make them less efficient. The others are all used multiple times, so by using the helper columns they only need to be calculated once each.

I did have more named ranges originally, but I've reduced that for simplicity and only used them where the number of rows needs to be variable.

There are 2 still 2 ranges not showing in the list, those are:-

INV_LR which refers to =MATCH(1E+100,INV!$C:$C)
ORD_LR which refers to =MATCH(1E+100,'Open Orders1'!$B:$B)

These are both use in other named ranges, but not in the actual sheet so the XL2BB add-in does not pick them up. @smozgur tagged for reference.

Hopefully this is of some use

Latest.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Schedule Ship Date	Item Number	Ordered Quantity	INTNL	On Hand	Bal After Order	FGINT	STG	PARG	FG	All	Only	First stat	First	Min Stat	Min	Prev Ord		Report 1 Update Status	Report 2 Status	Status
2	17/06/2020	492633-169	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0				0
3	06/10/2020	NPT	4	0	0	0	0	0	0	0	0	0	0	0	0	0	0				0
4	12/10/2020	453634-000	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0			Backordered	0
5	26/10/2020	T063DEW2364013H	24	0	336	312	0	336	0	1336	1672	6	1	1	0	0	0			Released to Warehouse	Staged
Open Orders1

Cell Formulas
Range	Formula
F2:F5	F2	=IF(L2=0,0,IF(IF(V2="Both",(H2+I2)>0),IF(OR(M2:N2=1),IF((H2+I2)>=D2,H2+I2,L2),IF(N2>1,IF((H2+I2)>=D2,P2,L2-R2),0)),IF(IF(V2="INTNL",H2>0),IF(OR(M2:N2=1),IF(H2>=D2,H2,SUM(H2,J2,K2)),IF(IF(N2>1,(H2-SUMIFS($D$1:$D1,$C$1:$C1,C2,$V$1:$V1,"Both"))>=D2),P2,0)),IF(IF(V2="Staged",(I2-R2)>0),IF(OR(M2:N2=1),IF(I2>=D2,I2,SUM(I2:K2)),IF(N2>1,IF(I2>=D2,Q2,IF((I2-R2)<D2,L2-R2,0)),0)),IF(V2=0,IF(L2=(J2+K2),IF(OR(M2:N2=1),J2+K2,IF(N2>1,IF((J2+K2)>=D2,P2,L2-R2),0)),IF(O2=1,L2-R2,IF(IF(O2>1,(L2-R2)>0),L2-R2,0))),IF(OR(IF(V2="INTNL",H2<=D2),IF(V2="Staged",I2<=D2),IF(V2="Both",(H2+I2)<=D2),OR(M2=1,O2=1)),J2+K2-R2,IF(IF(O2>1,OR(IF(V2="INTNL",H2<=D2),IF(V2="Staged",(I2-R2)<=D2),IF(V2="Both",(H2+I2)<=D2))),IF(SUM(COUNTIFS($C$1:$C1,C2,$V$1:$V1,{"Staged","Both","INTNL"}))>0,L2-R2,Q2),"x")))))))
G2:G5	G2	=IF(F2<=0,0,F2-D2)
H2:H5	H2	=IF(L2=0,0,SUMIFS(INVqty,INVItem,C2,INVsub,"FGINT"))
I2:I5	I2	=IF(L2=0,0,SUMIFS(INVqty,INVItem,C2,INVsub,"STG"))
J2:J5	J2	=IF(L2=0,0,SUMIFS(INVqty,INVItem,C2,INVsub,"PARG"))
K2:K5	K2	=IF(L2=0,0,L2-SUM(H2:J2))
L2:L5	L2	=SUMIFS(INVqty,INVItem,C2,INVsub,"<>YARD")
M2:M5	M2	=IF(L2=0,0,IF(O2>1,O2,COUNTIFS(ItemAll,C2)))
N2:N5	N2	=IF(L2=0,0,COUNTIFS($C$2:$C2,C2,$V$2:$V2,V2))
O2:O5	O2	=IF(L2=0,0,IF(N2>1,N2,COUNTIFS($C$2:$C2,C2)))
P2:P5	P2	=IF(OR(N2:O2>1),MINIFS($G$1:$G1,$C$1:$C1,C2,$V$1:$V1,V2),0)
Q2:Q5	Q2	=IF(OR(N2:O2>1),MINIFS($G$1:$G1,$C$1:$C1,C2),0)
R2:R5	R2	=IF(OR(N2:O2>1),SUMIFS($D$1:$D1,$C$1:$C1,C2),0)
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
INVItem	=INV!$A$2:INDEX(INV!$A:$A,INV_LR)	L2:L5, H2:J5
INVqty	=INV!$C$2:INDEX(INV!$C:$C,INV_LR)	L2:L5, H2:J5
INVsub	=INV!$B$2:INDEX(INV!$B:$B,INV_LR)	L2:L5, H2:J5
ItemAll	='Open Orders1'!$C$2:INDEX('Open Orders1'!$C:$C,ORD_LR)	L2, H2:J2, F2:F5, M2:R5

Sub inventory Possibilities include, but aren't limited to: FG, REPAIR, STG, RP, QC, FGINT, PARG, YARD, LEAN, MODOUT, FGFPK, TRNST, CHINA, MOD

 

[LHarting]

Why are you training IF statements and not using IFS?

I can't Ctrl+Shift to enter the array formulas, because this is mid-macro. How to I do array formulas in VBA?

 

[jasonb75]

IFS is less efficient than IF(IF(

I've reposted the formula below as a non-array version, there were only a couple of small parts that needed it, but they are easily changed.

=IF(L2=0,0,IF(IF(V2="Both",(H2+I2)>0),IF(OR(M2=1,N2=1),IF((H2+I2)>=D2,H2+I2,L2),IF(N2>1,IF((H2+I2)>=D2,P2,L2-R2),0)),IF(IF(V2="INTNL",H2>0),IF(OR(M2=1,N2=1),IF(H2>=D2,H2,SUM(H2,J2,K2)),IF(IF(N2>1,(H2-SUMIFS($D$1:$D1,$C$1:$C1,C2,$V$1:$V1,"Both"))>=D2),P2,0)),IF(IF(V2="Staged",(I2-R2)>0),IF(OR(M2=1,N2=1),IF(I2>=D2,I2,SUM(I2:K2)),IF(N2>1,IF(I2>=D2,Q2,IF((I2-R2)<D2,L2-R2,0)),0)),IF(V2=0,IF(L2=(J2+K2),IF(OR(M2=1,N2=1),J2+K2,IF(N2>1,IF((J2+K2)>=D2,P2,L2-R2),0)),IF(O2=1,L2-R2,IF(IF(O2>1,(L2-R2)>0),L2-R2,0))),IF(OR(IF(V2="INTNL",H2<=D2),IF(V2="Staged",I2<=D2),IF(V2="Both",(H2+I2)<=D2),OR(M2=1,O2=1)),J2+K2-R2,IF(IF(O2>1,OR(IF(V2="INTNL",H2<=D2),IF(V2="Staged",(I2-R2)<=D2),IF(V2="Both",(H2+I2)<=D2))),IF(SUM(COUNTIFS($C$1:$C1,C2,$V$1:$V1,{"Staged","Both","INTNL"}))>0,L2-R2,Q2),"x")))))))

With the additional possibilities for sub inventory, the formulas in H2:K2 will need changing to

=SUMIFS(INVqty,INVItem,C2,INVsub,"FGINT")

with the criteria matching the column heading for each of the 4 columns.

L2 will need to be changed to

=SUM(H2:K2)

 

[LHarting]

Thank you for all your help! I'll mark the above answer as solved. Again, thank you!

 

[smozgur]

These are both use in other named ranges, but not in the actual sheet so the XL2BB add-in does not pick them up. @smozgur tagged for reference.

@jasonb75 - Thanks for tagging.
That's correct. XL2BB currently doesn't pick the named ranges used in other defined names. It's been on the development list since the initial version, however, I am not quite sure when/if it will be implemented in the current major version.

 

[jasonb75]

Thanks for your feedback, @smozgur. I did wonder if it might have been a deliberate omission of something that might be rarely used but exceptionally difficult to code. I guess there has to be a limit as to how deep the code can drill into the core of the sheet. Names used in the range, names used in names used in the range, names used in names, used in names u... ?