Data Validation with 2 different if statements

Raiden

New Member
Joined
Jun 2, 2022
Messages
26
Office Version
  1. 2021
  2. 2019
Platform
  1. Windows
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.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Hi Raiden. If you could show a sample of your data it would be very helpful in providing you with a solution
Thanks
 
Upvote 0
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.
 
Upvote 0
Workbook.xlsx
DEFGH
5Sr.noDateBatch no.Order typeQty
600102-04-22*-*-4566Product_In1000
700212-04-22*-*-4566Dispatch300
800318-04-22#-#-7769Product_In1200
900415-04-22#-#-7769Dispatch500
Product_In
Cell Formulas
RangeFormula
D6D6=IF(E6="","",1)
D7:D9D7=IF(Dispatch_Process.xlsm!Product_Log_In[@Date]="","",D6+1)
Cells with Data Validation
CellAllowCriteria
G6:G9ListProduct_In,Dispatch,Rejection



Workbook.xlsx
CDEFGH
3Sr.noBatch no.Product_In QtyDispense QtyRejection QtyCurrent Stock
4001*-*-456610003000700
5002#-#-776912005000700
600300000
7  0000
8  0000
FG register
Cell Formulas
RangeFormula
C4C4=IF(D4="","",1)
D4:D8D4=IFERROR(INDEX(Product_In!F6:F9, MATCH(0, COUNTIF($D$3:D3,Product_In!F6:F9), 0)),"")
E4:E8E4=IFERROR(SUMIFS(Product_In!$H6:$H9,Product_In!$F6:$F9,D4,Product_In!$G6:$G9,"Product_In"),"")
F4:F8F4=IFERROR(SUMIFS(Product_In!$H6:$H9,Product_In!$F6:$F9,D4,Product_In!$G6:$G9,"Dispatch"),"")
G4:G8G4=IFERROR(SUMIFS(Product_In!$H6:$H9,Product_In!$F6:$F9,D4,Product_In!$G6:$G9,"Rejection"),"")
H4:H8H4=IFERROR(E4-F4-G4,"")
C5:C8C5=IF(D5="","",C4+1)
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D4:H8Cell Value=0textNO




Workbook.xlsx
CDEF
4Sr.noDateBatch.noQty
500125-May-22*-*-45661000
600226-May-22#-#-77691200
7 
Batch register
Cell Formulas
RangeFormula
C5C5=IF([@Date]="","",1)
C6:C7C6=IF([@Date]="","",C5+1)
 
Upvote 0
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.
 
Upvote 0
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"
 
Upvote 0
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)?
 
Upvote 0
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.
 
Upvote 0
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'
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top