Complex appraisal form

[anuradhagrewal]

Hi Guys
I have this appraisal form wherein I want to a score based on certain criteria to be calculated.
What I am looking is that if in the column titled "Please select the most relevant one from each heading" under A Intelligence I select say "Normally understands a new situation after proper explanations & handles it if given all directions"
Then this represents a rating of 2 . What I need is that if I select from the drop down using data validation tool as " Most relevant" then when I am calculating the total score of each heading I can automatically select a score or rating of 2 and this be added up in the end for all the Sub headers like A,B,C and so on.
Also if I select Most relevant in a given sub header say A then I cannot select another Most relevant in the same sub header A

How can I do this please guide me.

 

[Bosquedeguate]

Are "Most Relevant" and "Not Relevant" the only two choices for the column?

 

[Bosquedeguate]

Will this implementation work for you?

Here is a quick test mini-sheet. Row A below the 4 responses is blank when "Most Relevant" appears in the 4 rows above only once, if more than one time an error message appears.

B1:B2 is my response selection list.

Book2
	A	B
1	Most Relevant	Most Relevant
2	Not Relevant	Not Relevant
3	Most Relevant
4	Not Relevant
5	Most Relevant allowed once
Sheet1

Cell Formulas
Range	Formula
A5	A5	=IF(COUNTIF(A1:A4,"Most Relevant")>1,"Most Relevant allowed once","")

Cells with Data Validation
Cell	Allow	Criteria
A1:A4	List	=B$1:B$2

 

[anuradhagrewal]

Not working dear

 

[Bosquedeguate]

Not working dear

Please provide a copy of the Cell formula that is not working.

 

[Fluff]

Duplicate to: How to calculate score using criteria

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.