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]

I downloaded your workbook, and the VBA code you have in that file is NOT the VBA code I posted in my last post.

 

[Raiden]

I did paste the Same formula you posted.
easyupload.io here is the file again with the formula in Product_In sheet

 

[Joe4]

The file you posted in the previous link did NOT have the updated code (I think you may have accidentally pasted the original file).
This new one does. However, it still appears that you are using data tables on all the sheets.

I am using this new file you uploaded, and am not getting any errors.
Please walk me through a particular example, telling me what you are entering and where, that returns this error so I can try it for myself and see if I can recreate the error.

 

[Raiden]

So... If there is a new batch then we have to enter as "Product_In" ---> Enter Date in D7 then batch no in E7 & Qty in F7 ---> Then in 'Product_In' Sheet, Enter Date in E6 then Batch no. in F6 then select "Product_In" from drop-down list
If there is a Dispatch then---> Enter Date in E6 then Batch no. in F6 then select "Dispatch" from drop-down list & Enter Qty
I'm getting error here:-
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])"
There is no input in the Qty column
Am i doing something wrong here?

 

[Joe4]

Can you let me know the EXACT values you are entering in each of these cells, so I can try to reproduce the error on my side?

 

[Raiden]

The Exact Values are confidential.
So.. please use this :- *-*-4566
Enter Date in D7 then *-*-4566 in E7 & Qty in F7 ---> Then in 'Product_In' Sheet, Enter Date in E6 then *-*-4566 in F6 then select "Product_In" from drop-down list
If there is a Dispatch then---> Enter Date in E6 then *-*-4566 in F6 then select "Dispatch" from drop-down list & Enter Qty

 

[Joe4]

The Exact Values are confidential.
So.. please use this :- *-*-4566
Enter Date in D7 then *-*-4566 in E7 & Qty in F7 ---> Then in 'Product_In' Sheet, Enter Date in E6 then *-*-4566 in F6 then select "Product_In" from drop-down list
If there is a Dispatch then---> Enter Date in E6 then *-*-4566 in F6 then select "Dispatch" from drop-down list & Enter Qty

I don't care if you use "dummy values", I just want to recreate the error you are seeing.
You told me the exact value you are entering in E7, but not D7 or F7.
I want to follow the EXACT steps that you are doing to get this error, so I want to know the EXACT details of each step.

Also, do you really have "*" and "#" as characters of your Batch Numbers or is that just you "dummying" up the data?
I ask, because Excel could be interpretting those as wild-cards, which could lead to unintended consequences.
If it is just your attempt to "dummy" up the data for purposes of posting this here, it may be better to just use the last 4 characters, i.e. "4566".

 

[Raiden]

Yes.. Im only Dummying the data. As you said we can use 4566

Enter Date in D7 then 4566 in E7 & 1400 in F7 ---> Then in 'Product_In' Sheet, Enter Date in E6 then 4566 in F6 then select "Product_In" from drop-down list
If there is a Dispatch then---> Enter Date in E6 then 4566 in F6 then select "Dispatch" from drop-down list & Enter 200

 

[Joe4]

I cannot reproduce the error you are talking about.
When I open up the last file you uploaded, and enter those values in those cells, it successfully looks up and loads the value of 1400 in column H on the Product_In sheet for me.

Are you trying this on the exact same workbook as me (the last file you uploaded for me ), or on your "real" file?
If you have not tried it on the sample file you uploaded for me, try it and there and see if it works.

 

[Raiden]

Thank you very much Joe4 I tried this on my own PC & it worked, so it appears that my office PC has some issues. I just want to ask you If by mistake the operator choose "Product_In" instead of "Dispatch" can we clear contents after we try to add "Dispatch". I mean the "Product_In" formula will cleared.