×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel data validation custom formula

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

RE: Excel data validation custom formula

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?

RE: Excel data validation custom formula

(OP)
Yes. That is correct, magoo2.

RE: Excel data validation custom formula

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

RE: Excel data validation custom formula

(OP)
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.

RE: Excel data validation custom formula

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

 

RE: Excel data validation custom formula

(OP)
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.

RE: Excel data validation custom formula

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,




 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources