Score sheet

[DeletedAccount918]

I am currently working on a score sheet with list of question.

I have:
On Sheet 1 (will be hidden later)
List of 14 questions (e.g. question 1 = 6 possible answers, for answer 1 is score -2, for answer 2 is score 7... etc).
In column C are possible answers, in column D are the score values.

On Sheet 2 (will be visible for participants)
Drop-down list based on Sheet 1 questions (Sheet 1; Column C)
I have set Cell link for each question so when you choose the 3rd answer, there is number 3, but I need to set different values based on Sheet 1, could be that 3rd answer is score 12. I do not know which function to use (I tried IF, but to me it seems too long and difficoult).

Thanks in advance!
Tereza

 

[Splith]

This is the glory day for the Vlookup. The Vlookup will lookup from a list with different values from top to bottom so here we go.

The Vlookup syntax is such
=Vlookup(Lookup_Value, Table_Array, Col_Index_Num, [Range_Lookup]

Lookup_Value is that 3 that gets returned from selecting answer 3. This is the value that VLOOKUP will try to match your table against.

Table_Array is the Range of the information table (IF there is a table for each question then for each question the table array will have to be specific to the appropriate table for each question). This would include BOTH the Lookup Row and the Result Row and everything in between. (The lookup Row MUST be the Leftmost row so from column B:D it MUST be B)

Col_Index_Num (CIN) is simply asking where you want your data from. If for example CIN is 1. Then if your trying to match the answer 3 (lookup Value) with the first 3 in column A (Assuming the Table Array starts with Row 1) then for 1 is will return 3, if CIN is 2 it will return the value in column B, CIN = 3 then C etc.

Range Lookup is asking do you want it to guess at the closest answer or does it have to be the exact value. You can PM me if you would like to know more about this but for now just set this to FALSE.

 

[Splith]

Sorry for being General but to be more Specific your Table Array will be the Columns CX:DY X being the topmost row and Y being the bottom most row. Your CIN will be 2. Just remember to make sure that Each question has to have its own result table unless each of the tables are the same.

 

[DeletedAccount918]

I am sorry, but my knowledge of excel is not deep enough, maybe you could give me a hint with function example when I enclose "print screen"
Sheet 2 (for participants)

Sheet 1 (source - will be hidden later)

Thanks a lot for your help!

 

[DeletedAccount918]

Pictures:

Sheet 2
http://www.clichy.estranky.cz/fotoalbum/umelecke-pokusy/smeska-starsich-fotografii/original/74
Sheet 1
http://www.clichy.estranky.cz/fotoalbum/umelecke-pokusy/smeska-starsich-fotografii/original/75

 

[Splith]

Okay so now we have a little work to do.
The First and only change I made to your spreadsheets is on Sheet 1. After value and before Score I created a new column called "Value Ref". This column will be what we use for the Vlookup. I would like you to save what you have (in case for whatever reason you do not want to use this solution). Now I want you to Add that column is so from here out I will refer to cells as if that change has been made.

In the new column place the "Answer Number" (18 to 23 would be 1, 24 to 29 would be 2, 56 and over would be 6). Do this for every answer to every question, that shouldn't take more then 3-5 minutes.

Lets Get to work.
1. Make sure that Sheet2 is actually named Sheet2
2. Make sure that Sheet1 is actually named Sheet1


For the first question you want the score returned in (Sheet 2 Cell I3) place the below line in the cell I3,

=VLOOKUP($H3,'Sheet1'!D5:E10,2,False)

This is for Question 2 and is to be placed in cell I4 of sheet 2

=VLOOKUP($H4,'Sheet1'!$D$11:$E$17,2,False)

There is a pretty clear pattern there, for help with the equation look at my previous post, If you run into issues or this just plain and simple doesn't work at all make a reply and we will figure this out.

Want to really work this one out with you
~Splith

 

[DeletedAccount918]

Thanks a lot! Now it works and it's great
I just still do not understand why "2" is there (before False), but does not matter.. it works!

 

[Splith]

The 2 is the Column Index Number. I call it the CIN
The Vlookup takes the Left most section of the table and that is the number that it compares the Lookup_value to. But what if the value you want to return isn't right next to the lookup_value? What if you want a number 50 columns away. That is what CIN is for. what the 2 means is that in the table we want to return the Value 2 columns away from the left side. In my above example we could make it 50 and it would return 50 away from the left side. A value of 1 would mean that your lookup and return fields will be 1 in the same.

Im kinda bad at explaining but I think you get the picture.

~Splith.

 

[DeletedAccount918]

I got the idea, thanks.. for sure I will use this function it the future, it is very helpful! You are good at explaining, but english is not my native language so sometimes it's hard to understand.
Have a nice day!

 

[DeletedAccount918]

Thanks all for the help, now I have (hopefully) the last problem with my scorecard sheet. I need to put there a function which will write something regarding the total number.

I have:
Total score in Cell I19
I need to see in Cell C19 what means the total score regarding following conditions:
Score 168 - 120 will write "Approve"
Score 119 - 90 will write "Grey area"
Score 89 - 6 will write "Decline"
But in the same time I need it to write "Decline" in case there will be any "Decline" in the I3:I17 area.

I enclose picture to show what I mean.

Thanks again in advance!