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.

 

[Joe4]

The problem is you have too many linked formulas fighting with each other.

Column H on the "FG register" sheet is a formula, looking at columns E, F, and G on that sheet.
But column F is a formula pulling from the "Product_In" sheet, specifically column H value you just tried to update!
So the timing is getting messed up, since the Excel formulas are updated immediately.

Having all these inter-related formulas fighting with one another is not the way to go.
If you want to use Excel, I would recommend creating an entry form (User Form) to enter the data. Or have prompts for them to enter the data instead of entering it directly on the sheet.
Then you can perform all the necessary checks before writing the data to the worksheets.

Personally, I would use Microsoft Access for a project like this, as it is really a relational database project, and I like Access Forms a whole lot more than Excel User Forms.

 

[Raiden]

Okay... Thank you very much Joe for your assistance. I will convey my superiors about Microsoft access.

 

[Raiden]

Hey Joe, i am need of your assistance once again! I have resolved the issue which was emerging with code. i.e. the error with dispatch
i have changed a bit in the formula which looks like this

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 And mx < 0 Then
                MsgBox "Value in Current Stock cannot exceed " & mx, vbOKOnly, "ENTRY ERROR!"
                Target.Value = 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

Now my superiors are telling me to change the "Product_In" formula just like that of the "Dispatch" one which allows me to input only maximum allowable value
Can the formula be changed ??