Data validation / warning alert

I need to know how to add some data validation to a table. i have a copy of a test spreadsheet below. The idea is that this is sent out to staff. They enter their scores and employee level and then send it back.

If there employee level A there stats need to equal 100 if not an error needs to flag up. If there employee level B or C there score needs to add up to 80 or an error should flag.

Any idea?

Many thanks,

Chemist

You can't have data validation on a cell with a formula... the formula result does not trigger a flag... flags are triggered on manual entry cells.

You can either change Total formulas to something like:

=IF(SUM(B3:B7)<>100,"error",SUM(B3:B7)) where error will populate the cell if all cells don't sum to 100.

Or you can apply data validation to the last cell in the summation range, to say if sum is not equal to 100 it would give error after typing into that cell..

So you would select B20 and invoke Data Validation, then select Custom and enter formula"

=SUM(B3:B7)=100

and so on.

Because B8 is a calculated cell, you can't add Data Val to that cell. Several ways to do this. We could do a conditional format on B2:B7 or we can choose another cell (i.e. C8) and write a formula there. I chose the second. Let me know if you want the first.
In C8, I put
=IF(AND(B1="A",B8<100),"Total should be 100",IF(AND(B8>0,B8<80),"Total should be > 80",""))
Is this what you are looking for?

Ah ok thanks to you both. ChemistB this is what i a was after. Thanks loads.

Chemist

I should pay more attention... it didn't really click that you said you had the one table and that there was a drop down at the top...