Modifying an Inventory/Purchase Order formula containing multiple IF Functions

[ak_254]

Hi all,

I am having some difficulties with creating an inventory formula where on Sheet 1 I have inventory information where I keep key info such as the part number, current stock level of the part, and the status of any restock order pending. On Sheet 2 I have a list containing all restock orders currently being placed where in Cell D the Order Aging is determined if the order was received in Cell C. If Yes no calculations is made in Cell D, If No the Order Date is subtracted from Today's date calculating a number in Cell D.

I want to create a formula where on Sheet 1 Cell C it provides the current stock level of a part and a notification if a restock order is being placed.

The below formula is what I currently have and is working as intended with the exception of when a part is marked "Out of Stock" in Cell B on Sheet 1 with an restock order pending with an order aging from Sheet 2.

It currently produces the results with an order aging: "Out of Stock" (Should only state just "Out of Stock" if there is no current orders being placed on Sheet 2)
Should produce the result with an order aging from Sheet 2: "Out of Stock, Restock Order Placed"


On Sheet 1 Cell C4: IF(B4="Out of Stock","Out of Stock",IF(SUMIF('Sheet 2'!A:A,A4,'Sheet 2'!D:D)>0,B4&", Restock Order Placed","In Stock"))


Sheet 1. (Contains Inventory Info)

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Part Number
[/TD]
[TD]Stock Level
[/TD]
[TD]Order Status
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]123-111
[/TD]
[TD]High
[/TD]
[TD]In Stock
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]123-222
[/TD]
[TD]Low
[/TD]
[TD]Low, Restock Order Placed
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]123-333
[/TD]
[TD]Out of Stock
[/TD]
[TD](Out of Stock, Restock Order Placed)
[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2. (Contains Orders)

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Part Number
[/TD]
[TD]Order Date
[/TD]
[TD]Order Received
[/TD]
[TD]Order Aging
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]123-111
[/TD]
[TD]2/1/2018
[/TD]
[TD]Yes
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]123-222
[/TD]
[TD]2/5/2018
[/TD]
[TD]No
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]123-333
[/TD]
[TD]2/5/2018
[/TD]
[TD]No
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

Please let me know if anyone has any questions and any help or point in the right direction is greatly appreciated!

 

[AlphaFrog]

Give this a try...

=IF(B4="High","In Stock",B4 & IF(SUMIF('Sheet 2'!A:A,A4,'Sheet 2'!D:D)>0,", Restock Order Placed", ""))

 

[ak_254]

Give this a try...

=IF(B4="High","In Stock",B4 & IF(SUMIF('Sheet 2'!A:A,A4,'Sheet 2'!D:D)>0,", Restock Order Placed", ""))

Hi AlphaFrog,

Thanks for the response! That modification produced exactly the response I wanted for the status "Out of Stock" however in addition I was wondering if there was any way for when the Status is "Low" to state "In Stock" and if it is "Low" with an restock order placed with an order aging on Sheet 2 to state "Low, Restock Order Placed."

 

[AlphaFrog]

=CHOOSE((B4="High")+(B4="Low")*2+(B4="Out of Stock")*3+(SUMIF('Sheet 2'!A:A,A4,'Sheet 2'!D:D)>0)*2,B4,"In Stock",B4, "Low, Restock Order Placed", "Out of Stock, Restock Order Placed")