overwhelmed

[crmiko]

I am a middle school math teacher. I bought Power Excel (2019 edition) and was instantly overwhelmed. I am sure the book has the information to help me but I don't know what is called to do what I want.

We have to post grades with comments 4 times a year. The list of comments we can use are included in a drop-down menu. Not all the comments apply to what I want to say but I have to look through all the comments every time. If I make a list of all the comments and I put them in a table. Can I put in the grade percentage in and only have a selected few comments come up that apply to the percentage I put in?

If this is possible, my life would be easier.

Thanks

 

[nardagus]

Hi,

Hmmm. I'm not sure how grades should correspond to comments.
Will your pupils get
1) 0%,10%,50%,80%?
2) Or also 38%?

If case 2 is true then I'm not sure how to help you in an easy way. I'd use some helper columns to achieve it.

However if 1st case is true then it is quite easy.

We need to create two worksheets. For the sake of this post I will create:
1) Worksheet: grade_comments - this will hold info about grade vs comments
2) Worksheet: grades - this will hold your pupils grades with cells with drop-down

Worksheet "grade_comments" looks like this:

Excel 2016 (Windows) 32 bit[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Grade (%)[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD]Comment1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]10[/TD]
[TD]Comment2[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]10[/TD]
[TD]Comment3[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]10[/TD]
[TD]Comment4[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]20[/TD]
[TD]Comment5[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]20[/TD]
[TD]Comment6[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]20[/TD]
[TD]Comment7[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]20[/TD]
[TD]Comment8[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]30[/TD]
[TD]Comment9[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"]30[/TD]
[TD]Comment10[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"]30[/TD]
[TD]Comment11[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"]40[/TD]
[TD]Comment12[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: right"]40[/TD]
[TD]Comment13[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: right"]40[/TD]
[TD]Comment14[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: right"]40[/TD]
[TD]Comment15[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: right"]50[/TD]
[TD]Comment16[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: right"]50[/TD]
[TD]Comment17[/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: right"]50[/TD]
[TD]Comment18[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: right"]50[/TD]
[TD]Comment19[/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: right"]50[/TD]
[TD]Comment20[/TD]
[/TR]
</tbody>[/TABLE]

grade_comments​

That's all for "grade_comments".

Worksheet "grades" looks like this:

Excel 2016 (Windows) 32 bit[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Pupil[/TD]
[TD]Grade (%)[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Tom[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Mark[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Jessica[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Meggy[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

grades​

Ok. Now the tricky part..
For C2 you have to enable Data Validation (in Data tab). Pick List and in source field you have to put this formula:

Excel 2016 (Windows) 32 bit

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH][/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "][/TH]
[TD="align: left"]=INDIRECT("grade_comments!B" & MATCH(B2,grade_comments!A:A,0) & ":B" & MATCH(B2,grade_comments!A:A,0) +COUNTIF(grade_comments!A:A,B2)-1)[/TD]
[/TR]
</tbody>[/TABLE]

Accept changes with OK. When you select range cells from C2 to C5 and press CTRL+D on your keyboard data validation will be copied down to all selected cells. B2 from a formula will be updated for all cells (B2, B3, B4 and so on). However be sure not to put $ before row number (i.e. B$2) as this will prevent row update when you press CTRL+D.[/TD]
[/TR]
</tbody>[/TABLE]

 

[D1XII]

Excel can be overwhelming, but thank the Lord for templates! Check these out: http://web.mit.edu/jabbott/www/excelgradetracker.html

Or google excel grading templates and you should find quite a few free ones.

Hope that takes some of the frustration away!

 

[crmiko]

Thank you for posting an answer. I am going to give it a try and let you know. I am going to try your second option. If I can get it into a 10% range it will be easier than what I am doing now.

 

[Logit]

.
nardagus

Pardon my "jumping in" on this thread. I found the problem and solution very intriguing.

Having applied the formula using Data Validation .... Excel is telling me I can't reference a range from another sheet.

That is strange because I've perform Data Validation previously, using a range from another sheet, and not had any issues. Albeit, the
reference I was using didn't include the complex formula you have presented here.

Can you suggest a solution for Excel refusing to cooperate here ?

Thanks.

 

[nardagus]

Hello,

Sorry for late response.
To be honest I don't know how to help you. I'd have to look at your file. Maybe there is an error in a formula used for Data Validation. However it shouldn't tell you that you cannot have reference to another worksheet...

 

[Logit]

Thank you for responding.

Perhaps it is just another of those little querks my copy of Excel 2007 wants to express.
It has a number of them.

Cheers !