restricting data

[megawhammy]

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel how does one restrict data referring to a sum of cells?

allow me to explain: the sum of three cells equals 100%. if the sum of the three cells were anything other than 100%, a data restriction message appears.

thanks

 

[Bob Greenblatt]

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel
how does one restrict data referring to a sum of cells?

allow me to explain: the sum of three cells equals 100%. if the sum of
the three cells were anything other than 100%, a data restriction
message appears.

thanks

Try data validation. Look in Help for a more comlete explanation.

 

[CyberTaz]

Without VBA I don't see a way to do exactly what I understand you to want...
I don't believe Data> Validation will do it because the *content* of the
cell storing the SUM() function isn't being changed, it's just delivering a
variable result. If Data Validation can be triggered by a change in the
calculated result without using a macro I have no idea how to do it, but you
do have at least 2 options that may be viable alternatives:

Option 1

Use conditional formatting on the cell where the Sum() function is:
1- Format> Conditional Formatting
2- Set the 2nd list (comparison operator) to 'not equal to'
3- Set the value to 1
4- Choose how you want the cell to be formatted if the sum is something
other than 100%. A sum of 100% will display normally but any other result
will display with the formatting specified.

Option 2

Use an IF() function in addition to the SUM() function to return a text
string if the sum is other than 1. Assume the 3 values are in cells C1:C3 &
the sum is in cell C7, the formula would be:

=if(SUM(C1:C3)=1,SUM(c1:c3),"Invalid")

Replace the term 'Invalid' with any text string you prefer. That will appear
in cell C7 if the sum of C1:C3 doesn't equal 100%, otherwise 100% will
display in the cell. Optionally, the IF() statement could be written as:

=if(SUM(C1:C3)=1,"Good","Bad")

In that case C7 would display the word 'Good' if the sum is 100% but
otherwise would display the word 'Bad'.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[John McGhie]

Let's assume you have cells A1 to A4, with the total appearing in A4.

Set the data validation of A1, A2, and A3 to "Custom" and set the Formula to
=A4<=100 on all three cells.

You will get a warning when any of them changes if the total exceeds 100.

Cheers

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel how
does one restrict data referring to a sum of cells?

allow me to explain: the sum of three cells equals 100%. if the sum of the
three cells were anything other than 100%, a data restriction message appears.

thanks

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[CyberTaz]

By golly, I must have had my blinders on -- Didn't even consider coming in
the back door :-} Good call, John!

Regards |:>)
Bob Jones
[MVP] Office:Mac