Data Validation with 2 different if statements

[Raiden]

I am creating a system for Product log which has 3 sheets. 1st sheet is Batch card register which contains batch no. and quantity. 2nd sheet contains product log_In where input for dispatch, rejection and product -In is entered. 3rd sheet is a FG REGISTER which has list of rejection, dispatch and current stock . 3rd sheet is going to be locked.
The process of data input is as follows: Input of batch no with quantity in Batch card register --> Add the batch no. as a "Product_in"--> Add a dispatch if as a "Dispatch"
This is a drop-down which contains Product_In, Dispatch & Rejection

My plan is when i select "Product_In", the Qty of that particular batch is fetch automatically from Batch register & if i select "Dispatch" and if i enter a value it should be less that that of the Current stock of the Batch which has a reference from FG REGISTER.

Any Help is really appreciated.

 

[Raiden]

I make an entry in Batch register sheet : 20-5-22 (Cell D5)--->*-*-4566 (Cell E5)---> 1000 (Cell F5)
I make an entry in Product_In sheet: 20-5-22 (Cell E6)-->*-*-4566 (Cell F6)--> Product_In (Cell G6) [from Drop-down list]--> 1000 (Cell H6) [Should be fetched automatically using reference from Batch register] {This is automatically captured in FG register sheet )
i make an entry in Product_In sheet : 21-5-22 (Cell E7)-->*-*-4566 (Cell F7)--> Dispatch (Cell G7) [from Drop-down list]--> 300 (Cell H7) [Should have a Limiter so i can't enter value which is higher than Current Stock of FG Register]

I hope this is good enough to understand the process.
Sorry as this is my first time posting a question here

 

[Joe4]

So, regarding column H on your FG Register, it sounds like:
- For "Product_In" entries, you want it to automatically look up and return the value from the "Batch Register" sheet
- For "Dispatch" entries, you want to be able to manually enter in the amount, but have it limited by the "Current Stock" listing on the "FG Register" sheet
Is that correct?

It seems kind of odd that you want to sometimes manually populate column H, and other times you want a formula to return the value, as a cell can only contain a hard-coded value or a formula, and never both at the same time.

If that is indeed the case, we may need to incorporate the use of VBA. Is that acceptable?

 

[Raiden]

Yes... So i have a VBA code running in the Product_In sheet which is the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A1:AA1000")) Is Nothing Then
        ThisWorkbook.Save
    End If
Dim cl As Range
ActiveSheet.Unprotect "FGIM@22"
    For Each cl In Target.Cells
            If Target.Column = 10 Then
                check = MsgBox("NOTE: CANNOT be edited after confirmation, Confirm the Entry?", vbYesNo, "Confirm Entry")
                If check = vbYes Then
                    Range("A" & cl.Row & ":J" & cl.Row).Locked = True
                Else
                    Range("B" & cl.Row & ":H" & cl.Row).Locked = False
                End If
            End If
    Next cl
    ActiveSheet.Unprotect "FGIM@22"
       If Not Intersect(Target, Me.Range("A1:AA1000")) Is Nothing Then
           ThisWorkbook.Save
       End If
End Sub

 

[Raiden]

Yes... So i have a VBA code running in the Product_In sheet which is the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A1:AA1000")) Is Nothing Then
        ThisWorkbook.Save
    End If
Dim cl As Range
ActiveSheet.Unprotect "FGIM@22"
    For Each cl In Target.Cells
            If Target.Column = 10 Then
                check = MsgBox("NOTE: CANNOT be edited after confirmation, Confirm the Entry?", vbYesNo, "Confirm Entry")
                If check = vbYes Then
                    Range("A" & cl.Row & ":J" & cl.Row).Locked = True
                Else
                    Range("B" & cl.Row & ":H" & cl.Row).Locked = False
                End If
            End If
    Next cl
    ActiveSheet.Unprotect "FGIM@22"
       If Not Intersect(Target, Me.Range("A1:AA1000")) Is Nothing Then
           ThisWorkbook.Save
       End If
End Sub

Its not related to the functioning i want

 

[Joe4]

You have VBA code running against these sheets?
That could be an important detail.
Before proceeding any further, do you have any VBA code running against the other two sheets?

 

[Raiden]

Yes..
Batch register:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A1:AAA1000")) Is Nothing Then
        ThisWorkbook.Save
    End If
End Sub

FG register:

Sub IC()
    Dim oneCell As Range, newNote As String

    For Each oneCell In Range("C4:K4")
        With oneCell
            If oneCell.DisplayFormat.Interior.Color = RGB(255, 199, 206) Then
                newNote = MsgBox("Stock Update Necessary" & VBA.Constants.vbNewLine & "Check " & oneCell.Offset(-1, 0) & " for remaining stock in FG REGISTER", vbInformation, "Stock Inventory")
                If newNote = "False" Then
                    Exit For
                Else
                    .Show
                End If
            End If
        End With
    Next oneCell
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A1:AA1000")) Is Nothing Then
        ThisWorkbook.Save
    End If
End Sub

The 1st Code is present in the Product_In Sheet as a Command Button
And i have a Horizontal Table which includes product with their Current stock (C4:K4)

Whole Workbook:

Sub DateFolderSave()
Dim strGenericFilePath      As String: strGenericFilePath = "D:\"
Dim strYear                 As String: strYear = Year(Date) & "\"
Dim strMonth                As String: strMonth = MonthName(Month(Date)) & "\"
Dim strDay                  As String: strDay = Day(Date) & "\"
Dim strFileName             As String: strFileName = "Dispatch_Process"
Application.DisplayAlerts = False
' Check for year folder and create if needed
If Len(Dir(strGenericFilePath & strYear, vbDirectory)) = 0 Then
    MkDir strGenericFilePath & strYear
End If
' Check for month folder and create if needed
If Len(Dir(strGenericFilePath & strYear & strMonth, vbDirectory)) = 0 Then
    MkDir strGenericFilePath & strYear & strMonth
End If
' Check for date folder and create if needed
If Len(Dir(strGenericFilePath & strYear & strMonth & strDay, vbDirectory)) = 0 Then
    MkDir strGenericFilePath & strYear & strMonth & strDay
End If
' Save File
ActiveWorkbook.SaveAs FileName:= _
strGenericFilePath & strYear & strMonth & strDay & strFileName, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Application.DisplayAlerts = True
' Popup Message
MsgBox "File Saved As: " & vbNewLine & strFileName
End Sub

Private Sub Workbook_Open()

Dim DateDueCol As Range
Dim DateDue As Range
Dim NotificationMsg As String

Set DateDueCol = Worksheets("FG REGISTER").Range("C4:K4")

For Each DateDue In DateDueCol

    'Change H2 to the cell for bring forward reminder days in your data
    If DateDue.DisplayFormat.Interior.Color = RGB(255, 199, 206) Then
    'Change the offset value to pick up the invoice number column in your data
        NotificationMsg = NotificationMsg & " || " & DateDue.Offset(-1, 0)
    End If

Next DateDue

If NotificationMsg = "" Then

    MsgBox "Product Stock is Available."

Else: MsgBox "The following PRODUCT have Low Stock : " & NotificationMsg

End If


End Sub

Sub Save_Active_Workbook()
    ActiveWorkbook.Save
End Sub

The 1st Code is present in the Product_In Sheet as a Command Button

 

[Joe4]

The fact that some of your lists are tables, and trying to lookup from potential tables on other sheets makes things a bit tricky (Admittedly, I rarely use tables in Excel, so it is not my forte. If I have need of tables, I just use Microsoft Access instead, which I feel are much easier to work with when dealing with a relational database model, like you have).

I am having issues getting things to work properly in my attempts to recreate what you have. But I cannot even be certain that my re-creation accurately reflects what you have. I don't want to create a solution that doesn't work for you, because I have not set it up correctly.

If you would be willing to upload a simplified version of your workbook to a file sharing site on the internet, and provide a link to it here, I can download it when I get a chance, and see if I can come up with a solution that works for you.

 

[Raiden]

Is it possible for you to provide me with the VBA code for non-table version. I might try to change according to my needs.
If possible I will try to provide the link of the workbook.

 

[Joe4]

OK, try replacing the "Worksheet_Change" event procedure code in the "Product_In" sheet with this code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim check
    Dim cl As Range
    Dim mx As Double
    Dim batch
    Dim rng As Range
    
    ActiveSheet.Unprotect "FGIM@22"

    For Each cl In Target.Cells
        
        If Target.Column = 10 Then
            check = MsgBox("NOTE: CANNOT be edited after confirmation, Confirm the Entry?", vbYesNo, "Confirm Entry")
            If check = vbYes Then
                Range("A" & cl.Row & ":J" & cl.Row).Locked = True
            Else
                Range("B" & cl.Row & ":H" & cl.Row).Locked = False
            End If
        End If

'       Check to see if "Product_In" entry made in column G
        If Target.Column = 7 And Target.Value = "Product_In" Then
            Application.EnableEvents = False
'           Lookup value from Batch Register sheet and place in column H
            Target.Offset(0, 1).FormulaR1C1 = "=SUMIFS('Batch Register'!C[-2],'Batch Register'!C[-3],Product_In!RC[-2],'Batch Register'!C[-4],Product_In!RC[-3])"
            Application.EnableEvents = True
        End If
    
'       Verify entry in column H when "Dispatch" in is column G
        If Target.Column = 8 And Target.Offset(0, -1).Value = "Dispatch" Then
            Application.EnableEvents = False
'           Lookup value maximum allowable value from "FG Register" sheet
            batch = Target.Offset(0, -2)
            Set rng = Sheets("FG Register").Columns("D:H")
            mx = Application.WorksheetFunction.VLookup(batch, rng, 5, 0)
'           Limit entry to maximum allowed
            If Target.Value > mx Then
                MsgBox "Value in column H cannot exceed " & mx, vbOKOnly, "ENTRY ERROR!"
                Target.Value = mx
            End If
            Application.EnableEvents = True
        End If
    
    Next cl
          
    If Not Intersect(Target, Me.Range("A1:AA1000")) Is Nothing Then
        ThisWorkbook.Save
    End If
       
End Sub

 

[Raiden]

Thank you for the Code. I tried as you said but I'm getting a run-time error 'Type Mismatch' on ''
easyupload.io - This is the Excel Workbook of the same!