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'm Uploading the file with the interface of my "Real" File. easyupload.io
In this the column no. might change. I tried changing according to the real file but resulted in errors, so im asking for your help. Thank you for helping in advance.

 

[Raiden]

Okay so... i tried the Code on Office PC today and it is working good for "Product_In", but for "Dispatch" its weird. Whenever i enter a Qty to dispense for the particular batch (which is equal to or less than the batch value) it shows that i cant enter that value.
e.g. #-#-7770 (Batch Qty is 1500) in F11 ---> Dispatch in G11 ---> 1200 in H11
Pop-up Msg = Value in column H cannot exceed 300

 

[Joe4]

Okay so... i tried the Code on Office PC today and it is working good for "Product_In", but for "Dispatch" its weird. Whenever i enter a Qty to dispense for the particular batch (which is equal to or less than the batch value) it shows that i cant enter that value.
e.g. #-#-7770 (Batch Qty is 1500) in F11 ---> Dispatch in G11 ---> 1200 in H11
Pop-up Msg = Value in column H cannot exceed 300

I cannot replicate your issue, but then again I do not have your current data, and with all the automatic saves you have in the code, the original file has been written over many times over.

I think with all this interaction between multiple sheets, you are making this harder than it really has to be.
Really, Excel is not the best tool for something like this. What you really have is a relational database (where you have many different inter-related sheets).
Typically, relational database programs like Microsoft Access or SQL are much better for this sort of thing.

 

[Raiden]

I understand what you are saying, but my superiors have said to use excel for this project. I will try to send a video if possible to explain that. I have made changes for the "Real file" and it works except for this problem.

 

[Joe4]

I really don't know how much more assistance I can be on this.
We seem to be kind of "stuck" and spinning our wheels here, so maybe I am not the right guy to help you on this.
What makes it very difficult is that we seem to be in very different time zones, posting at very different times, and your problem is an involved one, and it seems I have to refresh and go through it from scratch every time I dive back into it.
And the time (like now) we are both on at the same time, I am at my work computer where I cannot download files from the internet.

 

[Raiden]

I know its difficult for you, but i would like your assistance one last time. If we succeed in that, its a win & if not then I will search for another alternative on my end if possible.

 

[Joe4]

If you can upload an example that demonstrates EXACTLY the issue you are having (so I don't have to try to rebuild it myself), I can try to take a look at it later when I am at a computer where I can download files.

 

[Raiden]

Okay so here is the Video of the error easyupload.io
In the video the pop-up is not shown so I'm attaching the Photos of the Results

 

[Joe4]

Can you also post the Excel file, as it appears at the beginning of the video, so I can follow the EXACT same steps that you do on the EXACT same data, and see what happens?

 

[Raiden]

Okay... easyupload.io This is the File . Earlier i posted a file which resembles the layout of my "Real" file, Can you try & input some data so that if any changes are needed we could do that ?? "Real" file layout easyupload.io