Excel data validation custom formula
Excel data validation custom formula
(OP)
Can someone help me with a formula for Excel 2003 please? I have a spreadsheet for cut sheet calculations which I have been asked to design a layout diagram on. There are a group of three cells for entering quantities of several optional door latch types. A maximum of two latches of any combination of types may be installed.
I would like to create a custom data validation formula to place in all three cells to allow a quantity of zero, one or two to be entered in each cell as long as the sum of the three cells do not exceed a quantity of two.
For example, given cells A1, A2, and A3: If one is entered in A1, one may also be entered into either A2 or A3, but not both. Or if two is entered in one cell, the other two will not allow any numbers except zero to be entered.
Does this make sense? Thanks for any assistance you may be able to provide.
Dave Lehman
I would like to create a custom data validation formula to place in all three cells to allow a quantity of zero, one or two to be entered in each cell as long as the sum of the three cells do not exceed a quantity of two.
For example, given cells A1, A2, and A3: If one is entered in A1, one may also be entered into either A2 or A3, but not both. Or if two is entered in one cell, the other two will not allow any numbers except zero to be entered.
Does this make sense? Thanks for any assistance you may be able to provide.
Dave Lehman





RE: Excel data validation custom formula
Something that actually locks out entries would require a VBA routine.
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
Chinese prisoner wins Nobel Peace Prize
RE: Excel data validation custom formula
If I have A1 = latch 1; A2 = latch 2; A3 = latch 3
Allowable combos are:
1
1
0
1
0
1
0
1
1
2
0
0
0
2
0
0
0
2
Is that correct?
RE: Excel data validation custom formula
RE: Excel data validation custom formula
RE: Excel data validation custom formula
For cell A1 in the Data Validation dialog on the Settings tab I selected:
Allow: Whole number
Data: between
Minimum: 0
Maximum: =IF(SUM(A2,A3)=2,0,IF(SUM(A2,A3)=1,1,2))
I did the same for each of the other cells, but I replaced the formula in the maximum field with the following:
A2 =if(sum(A1,A3)=2,0,if(sum(A1,A3)=1,1,2))
A3 =if(sum(A1,A2)=2,0,if(sum(A1,A2)=1,1,2))
I tried subtracting the sum of the three cells from 2, but it was too unstable; I had to use a separate formula for each cell based on the sum of the other two cells.
RE: Excel data validation custom formula
Set up conditional formatting to change the color of the cells if the sum of the three is different than 2.
For Excel 2003:
Select A1 through A3
Format > Conditional Formatting > Condition 1 > Formula is: =SUM($A$1:$A$3)<>2
Set the format pattern to red (or any other color)
For Excel 2007:
Home > Conditional Formatting > Manage Rules > New Rule > Use a formula to determine which cells to format: =SUM($A$1:$A$3)<>2
Set the format pattern to red (or any other color)
Applies to > $A$1:$A$3
RE: Excel data validation custom formula
RE: Excel data validation custom formula
Another way is to use data validation in combination with dynamic ranges.
The basic range (named rngList) will contain values:
0
1
2
The validation range for cell A1(named A1_list can be given as a formula:
=OFFSET(rngList,0,0,MAX(1,3-Sheet1!$A$2-Sheet1!$A$3),1)
similary
A2_List =OFFSET(rngList,0,0,MAX(1,3-Sheet1!$A$1-Sheet1!$A$3),1)
A3_List =OFFSET(rngList,0,0,MAX(1,3-Sheet1!$A$2-Sheet1!$A$1),1)
Apply appropriate validation ranges to cells A1, A2 and A3.
Data validation/List/ =A1_list
I attached the file with the solution.
Cheers,