Data Validation of a Cell must be a whole number less than or equal to another cell

[chris_bosten]

Hi,

I'm looking for a custom data validation formula for all the cells in column D to be a whole number less than or equal to a corresponding value on the same row in column I.

Module Name	Specialism	Trainer	Number of Trainees	Names of Trainees	Day of Week	Training Module ID	Training Duration (hrs)	Maximum Participants
FFS1	Aggregates	Ste	3	A, B, C	Monday	PATR017	3.5	5

Number of trainees in every row must not exceed the value in "maximum participants" in their respective row

Can you help?

Best wishes
Chris

 

[etaf]

in data validation , use custom
and formula is
=AND(D2=INT(D2),D2<=I2)

i have added data validation and also show the formula in K2

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Module Name	Specialism	Trainer	Number of Trainees	Names of Trainees	Day of Week	Training Module ID	Training Duration (hrs)	Maximum Participants
2	FFS1	Aggregates	Ste	5	A, B, C	Monday	PATR017	3.5	5		TRUE
Sheet1

Cell Formulas
Range	Formula
K2	K2	=AND(D2=INT(D2),D2<=I2)

Cells with Data Validation
Cell	Allow	Criteria
D2	Custom	=AND(D2=INT(D2),D2<=I2)

 

[Peter_SSs]

Another way is to use one of the standard Data Validation methods rather than 'Custom'..

 

[etaf]

much better way , I saw whole number but did not realise it had a MIN and MAX value - dah!! - should have looked

 

[chris_bosten]

Wow thanks everyone!

 

[etaf]

you are welcome