need additional criteria added to cell formula.

steve400243

Active Member
Joined
Sep 15, 2016
Messages
380
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.

VBA Code:
=IF(D3="","",IF(F3="","Received","Shipped"))
 

steve400243

Active Member
Joined
Sep 15, 2016
Messages
380
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Many thanks for this. I appreciate it. I will test tomorrow morning and revert back.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

steve400243

Active Member
Joined
Sep 15, 2016
Messages
380
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello, I keep getting this in cell A3. Any thoughts?

Copy of chicago Report1.xlsm
ABCDEFGHIJKLMNO
1Report Date8/4/20212 free days
2StatusON HAND# INTRK #Received DateOn Hand # OutShipped DateOrder DateLOCATIONSHIPPERPIECESP.O #COMPLETEDays at FTNDays of Storagecycle count
3#NAME?1231238/6/20218/4/2021-2#NAME?
4#NAME?1231238/6/20218/6/20218/4/20210#NAME?
5
6
On Hand
Cell Formulas
RangeFormula
B1B1=NOW()
M3:M4M3=IF(ISBLANK(F3),$B$1-D3,F3-D3)
N3:N4N3=IFS(M3<2,0,A3="shipped",F3-D3-2,A3="storage",$B$1-D3-2)
A3:A4A3=IFS(D3="","",M3>2,"Storage",AND(F3="",G3=""),"Received",F3<>"","Shipped",G3<>"","Order")
 

steve400243

Active Member
Joined
Sep 15, 2016
Messages
380
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?

VBA Code:
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

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNO
1Report Date8/4/2021 11:122 free days
2StatusON HAND# INTRK #Received DateOn Hand # OutShipped DateOrder DateLOCATIONSHIPPERPIECESP.O #COMPLETEDays at FTNDays of Storagecycle count
3Order1231238/6/20218/4/2021-1.533050
4Shipped1231238/6/20218/6/20218/4/202100
Sheet1
Cell Formulas
RangeFormula
B1B1=NOW()
M3:M4M3=IF(ISBLANK(F3),$B$1-D3,F3-D3)
N3:N4N3=IFS(M3<2,0,A3="shipped",F3-D3-2,A3="storage",$B$1-D3-2)
A3:A4A3=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

Active Member
Joined
Sep 15, 2016
Messages
380
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEFGHIJKLMNO
1Report Date8/4/2021 11:122 free days
2StatusON HAND# INTRK #Received DateOn Hand # OutShipped DateOrder DateLOCATIONSHIPPERPIECESP.O #COMPLETEDays at FTNDays of Storagecycle count
3Order1231238/6/20218/4/2021-1.533050
4Shipped1231238/6/20218/6/20218/4/202100
Sheet1
Cell Formulas
RangeFormula
B1B1=NOW()
M3:M4M3=IF(ISBLANK(F3),$B$1-D3,F3-D3)
N3:N4N3=IFS(M3<2,0,A3="shipped",F3-D3-2,A3="storage",$B$1-D3-2)
A3:A4A3=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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,955
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Sep 15, 2016
Messages
380
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Active Member
Joined
Sep 15, 2016
Messages
380
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.

VBA Code:
=IF(D3="","",IF(F3="","Received","Shipped"))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,955
Office Version
  1. 365
Platform
  1. Windows
The reason you are getting #Name is that the IFS function is only available in 2019 & 365.
How about
Excel Formula:
=IF(D3="","",IF(F3<>"","Shipped",IF(G3<>"","Order",IF(M3>2,"Storage","Received"))))
 
Solution
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,822
Messages
5,766,646
Members
425,366
Latest member
Mau15092000

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top