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.

 

[Haree]

Hi Raiden. If you could show a sample of your data it would be very helpful in providing you with a solution
Thanks

 

[Joe4]

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Raiden]

Workbook.xlsx
	D	E	F	G	H
5	Sr.no	Date	Batch no.	Order type	Qty
6	001	02-04-22	*-*-4566	Product_In	1000
7	002	12-04-22	*-*-4566	Dispatch	300
8	003	18-04-22	#-#-7769	Product_In	1200
9	004	15-04-22	#-#-7769	Dispatch	500
Product_In

Cell Formulas
Range	Formula
D6	D6	=IF(E6="","",1)
D7:D9	D7	=IF(Dispatch_Process.xlsm!Product_Log_In[@Date]="","",D6+1)

Cells with Data Validation
Cell	Allow	Criteria
G6:G9	List	Product_In,Dispatch,Rejection

Workbook.xlsx
	C	D	E	F	G	H
3	Sr.no	Batch no.	Product_In Qty	Dispense Qty	Rejection Qty	Current Stock
4	001	*-*-4566	1000	300	0	700
5	002	#-#-7769	1200	500	0	700
6	003	0	0	0	0	0
7			0	0	0	0
8			0	0	0	0
FG register

Cell Formulas
Range	Formula
C4	C4	=IF(D4="","",1)
D4:D8	D4	=IFERROR(INDEX(Product_In!F6:F9, MATCH(0, COUNTIF($D$3:D3,Product_In!F6:F9), 0)),"")
E4:E8	E4	=IFERROR(SUMIFS(Product_In!$H6:$H9,Product_In!$F6:$F9,D4,Product_In!$G6:$G9,"Product_In"),"")
F4:F8	F4	=IFERROR(SUMIFS(Product_In!$H6:$H9,Product_In!$F6:$F9,D4,Product_In!$G6:$G9,"Dispatch"),"")
G4:G8	G4	=IFERROR(SUMIFS(Product_In!$H6:$H9,Product_In!$F6:$F9,D4,Product_In!$G6:$G9,"Rejection"),"")
H4:H8	H4	=IFERROR(E4-F4-G4,"")
C5:C8	C5	=IF(D5="","",C4+1)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4:H8	Cell Value	=0	text	NO

Workbook.xlsx
	C	D	E	F
4	Sr.no	Date	Batch.no	Qty
5	001	25-May-22	*-*-4566	1000
6	002	26-May-22	#-#-7769	1200
7
Batch register

Cell Formulas
Range	Formula
C5	C5	=IF([@Date]="","",1)
C6:C7	C6	=IF([@Date]="","",C5+1)

 

[Joe4]

OK, based on the example you posted, can you walk us through an actual example with numbers.
So, tell us what values you are entering in which cells (give specific details, like "enterting 100 in cell G10 on the Product_In sheet"),
and then tell us exactly what should happen on the other sheets.

I think walking through a specific example like this will really clarify to us what you are trying to do.

 

[Raiden]

Okay, So first i will be entering batches i.e.*-*-4566 with Qty i.e. 1000 in Batch register which are finished products and ready to export.
There is no input manually needed in the FG Register as it is automated (Formulas).
The Batch no. is fetched from Product_In sheet and the batch no will not repeat. Product_IN, Dispatch & Rejection Qty is fetched based on order type and batch no from Product_In sheet.
Current stock is value obtained by subtracting values of Dispense and Rejection from Product_In Qty.
Next I will be entering information about the batch if it is Product_In, Dispatch or Rejection and the quantity.
i.e. Date: 2-4-22, Batch no.: *-*-4566, Order type is Product_In, Qty: 1000 (This Qty is the one i want to select based on Order type. If it is Product_In, Value should be fetched from Batch register and Dispatch Qty to be entered will be based on current stock. If the current stock is less and the Qty entered is more, Display msg as "Qty error"

 

[Joe4]

In looking at the formulas on each sheet, I see that some have the "@" symbol in them, and others don't.
The "@" symbol is used to indicate that the field is from a table.
So, that seems to be imply that the data on your "Batch register" sheet is in a table, but you are not using tables on the other two sheets.
Is that correct? You are only using Excel data tables on 1 of the 3 sheets?

And the formula in column D of the "Product_In" sheet also seems to be linking to another file ("Product_Log_In" sheet on the "Dispatch_Process.xlsm" file)?

 

[Joe4]

And I am having trouble following along with what you want/need to happen.

When I asked for an actual example, I would like you to tell me:
- EXACTLY what numbers/entries you are entering in. I want to know the Sheet you are entering it, the EXACT cell addresses you are entering them in, and the EXACT values you are entering in.
- Then, I want to know EXACTLY what you want/need/expect to happen ont he other sheets that is not currently happening. So tell me exactly which sheets, values, and cells should be updated, and EXACTLY what those cells should be updated to.

 

[Raiden]

No, every data in each sheet is in a table. And the formula is about serial no. (001,002) which you are talking about in 'Product_In'. Its the same as i have not dragged the formula (Sorry about that). When i enter my batch info in the 'Batch register', the 'Product_In' sheet will have a dropdown list of the batches which are entered. When I select the batch and select 'Product_In' in G column of 'Product_In' sheet, i should get the result as the Qty of the batch which is mentioned in 'Batch register' and if select 'Dispatch', i want to setup a (Kind of) limiter so i won't be able to enter Qty more than that of the Qty of 'Current stock' from 'FG register'

 

[Joe4]

You still haven't given me a specific example. I am a very visual person.
I want you to do a demonstration, to walk us through an actual example with specific numbers and cell addresses (remember, while this is familiar to you, and you know how you want it to work, we don't).
So walk us through an actual example, i.e.
"I make an entry on the Batch Register sheet, entering 27-May-22 in cell D7, 7769 in cell E7 and 500 in cell F7, etc."
Then what do you want to happen to the other sheets?
Tell me specific numbers and cells.
Details, details, details!