List Validation and IF Function Problem
 

I am trying to make three drop boxes, each containing a percentage eith an
incremation of 5% (5%,10%,15% etc.). I would like the second to be affected
by the first so the total selectable total is never over 100% (ie if box 1
says 10%, I can select 5% to 90%in box two), and the third to be affected
similarly by the second and first. As the first box is filled first it need
not be prohibited by the others.

To do this I am using an IF formula within the validation, but as I can only
use 7 IF Statements I can only get to a condition in which the first box
displays 35%. Is there a way to further stack the IF statements (either
within excel or using macros and visual basic), or is there a simpler way to
solve this problem?

List Validation and IF Function Problem
 

I am trying to make three drop boxes, each containing a percentage eith an
incremation of 5% (5%,10%,15% etc.). I would like the second to be
affected
by the first so the total selectable total is never over 100% (ie if box 1
says 10%, I can select 5% to 90%in box two), and the third to be affected
similarly by the second and first. As the first box is filled first it
need
not be prohibited by the others.

To do this I am using an IF formula within the validation, but as I can
only
use 7 IF Statements I can only get to a condition in which the first box
displays 35%. Is there a way to further stack the IF statements (either
within excel or using macros and visual basic), or is there a simpler way
to
solve this problem?

Let's say your 3 cells are A1, B1 and C1, then try these validation formulas
in Data/Validation/Custom...

A1: =MOD(A1,5)=0

B1: =AND(A1+B1<=100,MOD(B1,5)=0)

C1: =AND(A1+B1+C1<=100,MOD(C1,5)=0)

Rick

List Validation and IF Function Problem
 

If I understand what you want:

You want 3 cells with drop down lists.

The selections when totaled from all 3 should not be greater than 100.

If you select 10 from list 1 the selections available from list 2 should be
<=90.

If you select 50 from list 2 the selections available from list 3 should be
<=40.

Is this correct?

This is relatively easy to do however, you'd need an event macro that clears
the cells to the right of any cell that is changed. For example, if you
select 10 from list 1, then select 50 from list 2, so far so good since the
total is 60. But at this point you could change the selection from list 1 to
100 while the cell for list 2 still contains 50. The total of selections is
150.

--
Biff
Microsoft Excel MVP


"mmd206" wrote in message
...
I am trying to make three drop boxes, each containing a percentage eith an
incremation of 5% (5%,10%,15% etc.). I would like the second to be
affected
by the first so the total selectable total is never over 100% (ie if box 1
says 10%, I can select 5% to 90%in box two), and the third to be affected
similarly by the second and first. As the first box is filled first it
need
not be prohibited by the others.

To do this I am using an IF formula within the validation, but as I can
only
use 7 IF Statements I can only get to a condition in which the first box
displays 35%. Is there a way to further stack the IF statements (either
within excel or using macros and visual basic), or is there a simpler way
to
solve this problem?