Conditional input

[Zubair]

Hi,

I need restriction (Pop up message) while the user puts more quantity in cell B2 vs E2.

Product	Quantity Required		Product	Quantity available
Apple	8​		Apple	2​
Banana			Banana	4​
Orange			Orange	3​

 

[etaf]

setup a DATA validation with a formula
$B2<=$E2
and then you can put a message in the pop if you want

Book13
	A	B	C	D	E
1	Product	Quantity Required		Product	Quantity available
2	Apple	2		Apple	2
3	Banana			Banana	4
4	Orange			Orange	3
Sheet5

Cells with Data Validation
Cell	Allow	Criteria
B2:B4	Custom	=$B2<=$E2

 

[Zubair]

Hi etaf, its giving an error

This value doesn't match the data validation restrictions defined for this cell.

 

[etaf]

thats what you should have if the value in B entered is greater than value in E
did you use
=$B2<=$E2

I have saved to a share , but will only be on the share for a few days
I dont think XL2BB retains the data validation if copied into a spreadsheet

so you can open and see it working

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[Zubair]

Its working , Many thanks.

 

[Zubair]

DataVal-ETAF.xlsx
	A	B	C	D	E
1	Product	Quantity Required		Product	Quantity available
2	Apple	2		Apple	2
3	Apple	3		Banana	4
4	Orange			Orange	3
5
Sheet5

Cells with Data Validation
Cell	Allow	Criteria
B2:B4	Custom	=$B2<=$E2

How can we restrict if the user puts again the same product with quantity 3 in cell B3 as if the available quantity in E2 already consumed with the selection of B2?

 

[etaf]

need to compare the total required with the total available
a few ways to do that , I choose SUMIF()

=SUMIF($A$2:$A$10,A2,$B$2:$B$10)<=SUMIF($D$2:$D$10,A2,$E$2:$E$10)
Enter that into the Data Validation

In case in the Quantity available there happens to be more than one entry
Otherwise you would maybe use a lookup for quantity available, if there is only one - but a sumif() is just as easy

heres the xl2bb and also i updated the share

DataVal-ETAF.xlsx
	A	B	C	D	E
1	Product	Quantity Required		Product	Quantity available
2	Apple	6		Apple	8
3	apple			Banana	4
4	Orange	1		Orange	3
5	Orange	2
6	Orange
Sheet5

Cells with Data Validation
Cell	Allow	Criteria
B2:B6	Custom	=SUMIF($A$2:$A$10,A2,$B$2:$B$10)<=SUMIF($D$2:$D$10,A2,$E$2:$E$10)

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[Zubair]

Very good and interesting, Many thanks etaf

 

[etaf]

you are welcome