Data validation using external cell contents

  • Thread starter Thread starter Frozen_Smoke
  • Start date Start date
F

Frozen_Smoke

I have three cells, A1:A3, whose sum is subtracted from 100 and store
in B1. I want to restrict the range of values allowed in cells A1:A
to [0 - contents of B1]. For example:

A1 = 10
A2 = blank
A3 = blank

B1 = 90

So, I want to be able to place any value from 0 - 90 in cell A2 or A3.
If I use data validation and specify the data must be between 0 and B1
then I am only allowed to enter a number from 0 to 45 in either A2 o
A3. If I enter 46, B1 is updated to a value of 44, which causes a
invalid entry. I am more or less trying to distribute a bucket o
'points' (in B1) amongst three other buckets (A1:A3). Is thi
possible
 
Hi
try the following
- select your cells A1:A3
- goto 'Data Validation' and enter the following formula:
=SUM($A$1:$A$3)<=100

Frank
 
Select cells A1:A3
Choose Data>Validation
From the 'Allow' dropdown, choose Custom
In the Formula box, type: =SUM($A$1:$A$3)<=100
Click OK

Frozen_Smoke < said:
I have three cells, A1:A3, whose sum is subtracted from 100 and stored
in B1. I want to restrict the range of values allowed in cells A1:A3
to [0 - contents of B1]. For example:

A1 = 10
A2 = blank
A3 = blank

B1 = 90

So, I want to be able to place any value from 0 - 90 in cell A2 or A3.
If I use data validation and specify the data must be between 0 and B1,
then I am only allowed to enter a number from 0 to 45 in either A2 or
A3. If I enter 46, B1 is updated to a value of 44, which causes an
invalid entry. I am more or less trying to distribute a bucket of
'points' (in B1) amongst three other buckets (A1:A3). Is this
possible?
Click to expand...
 
Back
Top