Data Validation - Min and Max Help Please

[Sbugle]

Hi,
I am trying to limit the number entered into two cells. One is the number of times a program can be offered in a year and the other is the number of months it will run during the year. For example, I run a course that goes for 3 months of the year and I run it 4 times a year. Cell A1 is the number of times I run it a year (4) and A2 is the number of times a year I offer enrolments (3). Each of these cells must be a number between 1 and 12 but A1*A2 must not exceed 12.

How do I set up a data validation for this?

Thank you so much! I am going round the bend with this haha.

 

[mabbutt]

Use data validation with a custom formula like this:

=SUM($A$1+$B$1)<=12

Use this data validation in both cells and if you want to set a warning message use the error alert tab.

 

[Sbugle]

Use data validation with a custom formula like this:

=SUM($A$1+$B$1)<=12

Use this data validation in both cells and if you want to set a warning message use the error alert tab.

Thanks mabbutt but it is not the sum that I need it is the multiplication of A1 and A2. When I use this formula it will allow a number greater than 12 in the first cell entered as the other cell is still registering as 0.

For example - I have tried to use the custom data validation of (A1*A2)<=12 into each of the cells but if I am entering into A1 first I can enter 13 because 13*0 is <=12.

Any other suggestions?

 

[Peter_SSs]

Any other suggestions?

Select A1 & A2 and apply this Data validation. Note the setting re 'Ignore blank'.

 

[Sbugle]

Select A1 & A2 and apply this Data validation. Note the setting re 'Ignore blank'.

View attachment 39865

Thank you, thank you, thank you!!!

 

[Peter_SSs]

You're welcome. Glad to help.