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

 

[smozgur]

Names used in the range, names used in names used in the range, names used in names, used in names u... ?

Exactly However, we are surely trying to implement anything doable that would make it a better tool. Thanks again for all your help to make it a better tool.

 

[jasonb75]

Why are you training IF statements and not using IFS?

Elaborating on why I said that IFS was less efficient.

If you look at the formula

=IFERROR(IFS(A1=1,"result1",A1=2,"result2",A1=3,"result3"),0)

even if the first result, A1=1 is true, the other sections are all still calculated even though they are not needed.

Using

=IF(A1=1,"result1",IF(A1=2,"result2",IF(A1=3,"result3",0)))

with the first result, A1=1 being true, the other sections are bypasses without being calculated.

The amount of effort wasted in calculating the unnecessary steps using IFS will be variable depending on the complexity of the calculations used, but it will always be less efficient than the equivalent nested IF's formula.

IFS is good for simplicity with very basic logical tests (as in the example I've used in this post) but that is about all.

You will also see that I took the unusual step of using

=IF(IF(A1=1,A2=1),"Result1","Result2")

instead of the more common

=IF(AND(A1=1,A2=1),"Result1","Result2")

similar to the IFS method, with AND, all of the logical tests are carried out regardless of the individual results. With the double IF method, if the first is false, the second is irrelevant and is skipped.

You can do something similar with OR as well, but I was going into meltdown by the time I got to that point.

=IF(OR(A1=1,A2=1),"Result1","Result2")

=IF(IF(A1=1,1,IF(A2=1)),"Result1","Result2")

with this, if the first is true then that is good enough so the second one is skipped.
In the case of the AND / OR methods, it is best to put the simplest logical test first, so that the more complex one is eliminated in the cases where it is not needed.

 

[jasonb75]

Thanks again for all your help to make it a better tool.

Anything that saves me a bit of typing in a future post, if it happens to help someone else as well... ?

 

[LHarting]

@jasonb75 I don't get why is this not calculating correctly? I get why it isn't calculating on the INTNL lines, but it should have 550 for the On Hand amount in the second line. I pulled the information from the Orc INV sheet (INV in my previous example.) and pasted it below the data to show there IS inventory.
Of course there is hidden named ranges INV_LAST_ROW which refers to =MATCH(1E+100,INV!$C:$C) and ORD_LAST_ROW which refers to =MATCH(1E+100,'Open Orders1'!$B:$B)

Orders to Release Work Macro3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Key	Schedule Ship Date	Item Number	Ordered Quantity	Total $	INTNL	On Hand	Bal After Order	Ship	Del. Report Update Status	Oracle Status	FGINT	STG	FG & PARG	Inv. On Hand	Only	First Stat.	First	Min Stat.	Min	Allocated	Both / INTNL / Staged
2	10633841.1CHZ	4/6/2021	CHZ	600	$22,674.00	INTNL	0	0	No Inventory		Ready to Release	0	550	0	550	3	1	1	0	0	0	INTNL
3	10527732.1CHZ	4/9/2021	CHZ	550	$20,784.50	INTNL	0	0	INTNL Ship	Staged/Pick Confirmed	Staged/Pick Confirmed	0	550	0	550	2	1	2	0	0	600	Both
4	10607246.1CHZ	4/9/2021	CHZ	200	$7,558.00	INTNL	-550	0	No Inventory		Ready to Release	0	550	0	550	2	2	2	0	0	550	INTNL
5
6	Orc INV SHEET
7	ITEM	SUBINVENTORY_CODE	QTY
8	CHZ	STG	120
9	CHZ	STG	120
10	CHZ	STG	70
11	CHZ	STG	120
12	CHZ	STG	120
Open Orders1

Cell Formulas
Range	Formula
G2:G4	G2	=IF(OR($O2=0,($O2-$U2)<0),0,IF(IF($V2="Both",($L2+$M2)>0),IF(OR($P2=1,$Q2=1),IF(($L2+$M2)>=$D2,$L2+$M2,$O2),IF($Q2>1,IF(($L2+$M2)>=$D2,$S2,$O2-$U2),0)),IF(IF($V2="INTNL",$L2>0),IF(OR($P2=1,$Q2=1),IF($L2>=$D2,$L2,SUM($L2,$N2)),IF(IF($Q2>1,($L2-SUMIFS($D$1:$D1,$C$1:$C1,$C2,$V$1:$V1,"Both"))>=$D2),$S2,0)),IF(IF($V2="Staged",($M2-$U2)>0),IF(OR($P2=1,$Q2=1),IF($M2>=$D2,$M2,SUM($M2:$N2)),IF($Q2>1,IF($M2>=$D2,$T2,IF(($M2-$U2)<$D2,$O2-$U2,0)),0)),IF($V2=0,IF($O2=$N2,IF(OR($P2=1,$Q2=1),$N2,IF($Q2>1,IF($N2>=$D2,$S2,$O2-$U2),0)),IF($R2=1,$O2-$U2,IF(IF($R2>1,($O2-$U2)>0),$O2-$U2,0))),IF(OR(IF($V2="INTNL",$L2<=$D2),IF($V2="Staged",$M2<=$D2),IF($V2="Both",($L2+$M2)<=$D2),OR($P2=1,$R2=1)),$N2-$U2,IF(IF($R2>1,OR(IF($V2="INTNL",$L2<=$D2),IF($V2="Staged",($M2-$U2)<=$D2),IF($V2="Both",($L2+$M2)<=$D2))),IF(SUM(COUNTIFS($C$1:$C1,$C2,$V$1:$V1,{"Staged","Both","INTNL"}))>0,$O2-$U2,$T2),"x")))))))
H2:H4	H2	=IF($G2<=0,0,$G2-$D2)
L2:M4	L2	=SUMIFS(InvQty,InvItem,$C2,SUB,L$1)
N2:N4	N2	=SUMIFS(InvQty,InvItem,$C2,SUB,"FG")+SUMIFS(InvQty,InvItem,$C2,SUB,"PARG")
O2:O4	O2	=SUM($L2:$N2)
P2:P4	P2	=IF($O2=0,0,IF($R2>1,$R2,COUNTIFS(Item,$C2)))
Q2:Q4	Q2	=IF($O2=0,0,COUNTIFS($C$2:$C2,$C2,$V$2:$V2,$V2))
R2:R4	R2	=IF($O2=0,0,IF($Q2>1,$Q2,COUNTIF($C$2:$C2,$C2)))
S2:S4	S2	=IF(OR($Q2:$R2)>1,MINIFS($H$1:$H1,$C$1:$C1,$C2,$V$1:$V1,$V2),0)
T2:T4	T2	=IF(OR($Q2:$R2)>1,MINIFS($H$1:$H1,$C$1:$C1,$C2),0)
U2:U4	U2	=IF(OR($Q2:$R2)>1,MINIFS($D$1:$D1,$C$1:$C1,$C2),0)

Named Ranges
Name	Refers To	Cells
'Orc INV'!_FilterDatabase	='Orc INV'!$A$1:$C$6	L2:N4
InvItem	='Orc INV'!$A$2:INDEX('Orc INV'!$A:$A,INV_LAST_ROW)	L2:N4
InvQty	='Orc INV'!$C$2:INDEX('Orc INV'!$C:$C,INV_LAST_ROW)	L2:N4
Item	='Open Orders1'!$C$2:$C$4	P2:U4, L2:N2, G2:G4
SUB	='Orc INV'!$B$2:INDEX('Orc INV'!$B:$B,INV_LAST_ROW)	L2:N4

 

[jasonb75]

It looks as if you may have pointed part of the formula to the wrong range when adapting it to your sheet, but there may be another problem that needs fixing as well. I'll wait until you confirm so that I can work on both problems at the same time rather than possibly having to change some parts twice.

I notice that you've changed SUMIFS to MINIFS in column U, but when I change that in mine the results are still different so there is something else that I haven't found yet.

What should the third row be showing in the example? I've just tried the same data on my test sheet and these are the results that I'm getting.
(going back to the earlier post where you broke down your original formula, which path should each in the new example be following)?

Follow up test.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
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	Status
2	04/06/2021	CHZ	600	0	0	0	0	550	0	0	550	3	1	1	0	0	0	INTNL
3	04/09/2021	CHZ	550	0	550	0	0	550	0	0	550	2	1	2	0	0	600	Both
4	04/09/2021	CHZ	200	0	-1150	0	0	550	0	0	550	2	2	2	0	0	1150	INTNL
Open Orders1

Cell Formulas
Range	Formula
F2:F4	F2	=IF(L2=0,0,IF(IF(S2="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(S2="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,$S$1:$S1,"Both"))>=D2),P2,0)),IF(IF(S2="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(S2=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(S2="INTNL",H2<=D2),IF(S2="Staged",I2<=D2),IF(S2="Both",(H2+I2)<=D2),OR(M2=1,O2=1)),J2+K2-R2,IF(IF(O2>1,OR(IF(S2="INTNL",H2<=D2),IF(S2="Staged",(I2-R2)<=D2),IF(S2="Both",(H2+I2)<=D2))),IF(SUM(COUNTIFS($C$1:$C1,C2,$S$1:$S1,{"Staged","Both","INTNL"}))>0,L2-R2,Q2),"x")))))))
G2:G4	G2	=IF(F2<=0,0,F2-D2)
H2:H4	H2	=SUMIFS(INVqty,INVItem,C2,INVsub,"FGINT")
I2:I4	I2	=SUMIFS(INVqty,INVItem,C2,INVsub,"STG")
J2:J4	J2	=SUMIFS(INVqty,INVItem,C2,INVsub,"PARG")
K2:K4	K2	=SUMIFS(INVqty,INVItem,C2,INVsub,"FG")
L2:L4	L2	=SUM(H2:K2)
M2:M4	M2	=IF(L2=0,0,IF(O2>1,O2,COUNTIFS(ItemAll,C2)))
N2:N4	N2	=IF(L2=0,0,COUNTIFS($C$2:$C2,C2,$S$2:$S2,S2))
O2:O4	O2	=IF(L2=0,0,IF(N2>1,N2,COUNTIFS($C$2:$C2,C2)))
P2:P4	P2	=IF(OR(N2=1,O2>1),MINIFS($G$1:$G1,$C$1:$C1,C2,$S$1:$S1,S2),0)
Q2:Q4	Q2	=IF(OR(N2=1,O2>1),MINIFS($G$1:$G1,$C$1:$C1,C2),0)
R2:R4	R2	=IF(OR(N2=1,O2>1),SUMIFS($D$1:$D1,$C$1:$C1,C2),0)

Named Ranges
Name	Refers To	Cells
INVItem	=INV!$A$2:INDEX(INV!$A:$A,INV_LR)	H2:K4
INVqty	=INV!$C$2:INDEX(INV!$C:$C,INV_LR)	H2:K4
INVsub	=INV!$B$2:INDEX(INV!$B:$B,INV_LR)	H2:K4
ItemAll	='Open Orders1'!$C$2:INDEX('Open Orders1'!$C:$C,ORD_LR)	H2:K2, F2:F4, M2:R4

 

[jasonb75]

Following up on my reply above, I have found another difference.

You've changed the first criteria in the main formula to

OR($O2=0,($O2-$U2)<0))

In the second row that will evaluate to OR($O2=0,(550-600)<0)) which is true, so the result of 0 would be correct for that row.