need additional criteria added to cell formula.

[steve400243]

Hello, I use this cell formula to return the data to cell A3 - "Received or Shipped" I also need ""Order" to populate if a date is entered into cell G3. and "Storage" to Populate if M3 >2. Changing each time new dates are entered in to cells D3, G3, and F3 and when cell M3 is greater than 2.

Hope that makes sense? Please let me know if you need additional information. Thank you for your time.

=IF(D3="","",IF(F3="","Received","Shipped"))

 

[steve400243]

Many thanks for this. I appreciate it. I will test tomorrow morning and revert back.

 

[steve400243]

Hello, I keep getting this in cell A3. Any thoughts?

Copy of chicago Report1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Report Date	8/4/2021												2 free days
2	Status	ON HAND# IN	TRK #	Received Date	On Hand # Out	Shipped Date	Order Date	LOCATION	SHIPPER	PIECES	P.O #	COMPLETE	Days at FTN	Days of Storage	cycle count
3	#NAME?	123	123	8/6/2021			8/4/2021						-2	#NAME?
4	#NAME?	123	123	8/6/2021		8/6/2021	8/4/2021						0	#NAME?
5
6
On Hand

Cell Formulas
Range	Formula
B1	B1	=NOW()
M3:M4	M3	=IF(ISBLANK(F3),$B$1-D3,F3-D3)
N3:N4	N3	=IFS(M3<2,0,A3="shipped",F3-D3-2,A3="storage",$B$1-D3-2)
A3:A4	A3	=IFS(D3="","",M3>2,"Storage",AND(F3="",G3=""),"Received",F3<>"","Shipped",G3<>"","Order")

 

[steve400243]

I am also using this VBA to Move the Row to the Shipped Tab once Shipped. Maybe causing it to not work with both Cell formulas and VBA?

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim r As Long
    Dim c As Long
    Dim LastRow As Long

'   Only run when a single cell is updated
    If Target.CountLarge > 1 Then Exit Sub
  
'   Get current row and column of Target that was just updated
    c = Target.Column
    r = Target.Row
  
'   Check to see if column C or E updated
    If c = 3 Or c = 5 Then
        Application.EnableEvents = False
'       Update cell to right with date
        Target.Offset(0, 1) = Now()
'       Check to see if column A is "Shipped"
        If Cells(r, "A") = "Shipped" Then
'           Find last row on shipped tab
            LastRow = Sheets("shipped").Cells(Rows.Count, "A").End(xlUp).Row + 1
'           Copy and paste row to "Shipped" tab
            Range(Cells(r, 1), Cells(r, 15)).Copy
            Sheets("shipped").Range("A" & LastRow).PasteSpecial (xlPasteValues)
            Application.CutCopyMode = False
'           Delete original row
            Rows(r).Delete
        End If
        Application.EnableEvents = True
    End If

End Sub

 

[Crystalyzer]

not sure, but I copied your XL2BB data where you were getting the #NAME? error in columns A and N above into a completely new workbook\worksheet and this is what shows

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Report Date	8/4/2021 11:12												2 free days
2	Status	ON HAND# IN	TRK #	Received Date	On Hand # Out	Shipped Date	Order Date	LOCATION	SHIPPER	PIECES	P.O #	COMPLETE	Days at FTN	Days of Storage	cycle count
3	Order	123	123	8/6/2021			8/4/2021						-1.53305	0
4	Shipped	123	123	8/6/2021		8/6/2021	8/4/2021						0	0
Sheet1

Cell Formulas
Range	Formula
B1	B1	=NOW()
M3:M4	M3	=IF(ISBLANK(F3),$B$1-D3,F3-D3)
N3:N4	N3	=IFS(M3<2,0,A3="shipped",F3-D3-2,A3="storage",$B$1-D3-2)
A3:A4	A3	=IFS(D3="","",M3>2,"Storage",AND(F3="",G3=""),"Received",F3<>"","Shipped",G3<>"","Order")

Is the XL2BB data from the "Shipped" tab or the original?

 

[steve400243]

not sure, but I copied your XL2BB data where you were getting the #NAME? error in columns A and N above into a completely new workbook\worksheet and this is what shows

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Report Date	8/4/2021 11:12												2 free days
2	Status	ON HAND# IN	TRK #	Received Date	On Hand # Out	Shipped Date	Order Date	LOCATION	SHIPPER	PIECES	P.O #	COMPLETE	Days at FTN	Days of Storage	cycle count
3	Order	123	123	8/6/2021			8/4/2021						-1.53305	0
4	Shipped	123	123	8/6/2021		8/6/2021	8/4/2021						0	0
Sheet1

Cell Formulas
Range	Formula
B1	B1	=NOW()
M3:M4	M3	=IF(ISBLANK(F3),$B$1-D3,F3-D3)
N3:N4	N3	=IFS(M3<2,0,A3="shipped",F3-D3-2,A3="storage",$B$1-D3-2)
A3:A4	A3	=IFS(D3="","",M3>2,"Storage",AND(F3="",G3=""),"Received",F3<>"","Shipped",G3<>"","Order")

Is the XL2BB data from the "Shipped" tab or the original?

That is strange, I open a new workbook and put the new formulas in and get the error again. I'm going to keep trying, Maybe look at just using VBA and get rid of cell formulas? thanks for helping.

 

[steve400243]

Reposting question in another thread.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[steve400243]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I'm Using 2016, will update my account details, my apologies.

 

[steve400243]

Hello, This formula was provided by Fluff, works great but I need to add to it. If the Date is entered by a user in cell G3, "Ordered" will populate in A3. and if cell N3 is greater than 2, "On Storage" in A3. I may not have explained what was needed correctly. Please have look at the above help provided.

=IF(D3="","",IF(F3="","Received","Shipped"))

 

[Fluff]

The reason you are getting #Name is that the IFS function is only available in 2019 & 365.
How about

=IF(D3="","",IF(F3<>"","Shipped",IF(G3<>"","Order",IF(M3>2,"Storage","Received"))))