Excel data validation custom formula

[dl08in]

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

 

[IRstuff]

It really depends on how much you're willing to invest. Something that simply warns the user about the issue might possibly be done with conditional formatting and IF statements.

Something that actually locks out entries would require a VBA routine.

TTFN

FAQ731-376
Chinese prisoner wins Nobel Peace Prize

 

[magoo2]

Let me make sure I understand what you want.

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?

 

[dl08in]

Yes. That is correct, magoo2.

 

[dl08in]

I got it. I'll post the answer I came up with later tonight when I have time. Thanks for the replies.

 

[dl08in]

For each cell I selected Data-->Validation...

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.

 

[chemebabak]

Try this:

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

 

[dl08in]

Thank you for the tip chemebabak. The way it worked out, it is not possible to enter an out of range number, but this would be a good alternative.

 

[yakpol]

dI08in,

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,