Excel Lookup/Search Tip 4 - Vlookup Explanation 3 - Vlookup to Assign Grades to Students

Hi guys,

I am a teacher and I need help on how I can create an excel file with my students' grades. I want them to access their grades by keying in their ID number. They can only see their grades for the various activities like exams or homeworks by inputting their ID numbers. I would really appreciate any help on this. Thanks a lot.

Numbero Uno - My Question

OK I will tell you my problem before I tell you how I have tried to solve it.

I would like to have, in my form, a field that is able to tell me what grade a student got based on their final marks. The grade would be 70+ A, 60+ B, 50+C etc. How would one do this in access?



Part Deux - Where I tried to figure it out on my own but failed.


This is my lovely efficient forumla from excel:

=IF(L7>69.49,"A",IF(L7>59.49,"B", IF(L7>49.49,"C",IF(L7>39.49,"D","F"))))

As you can see it finds out whether a student received an A, B, etc. Simples!

However so far all I have discovered in Acces is to input this into the Control Source:

=IIf([Module One Final Mark]>69.49,"A","F")

I tried inputting the same kind of categories into the control source but it was given answers as -1. The AND button is also obviously pointless, from what I can tell it works fine on Queries but the AND button seems to do the same here as in Excel so.. I do not except it to be useful to me.

Any suggestions? I would rather keep out of VBA but I am starting to think I will have to start teaching myself how to do that as well to sort this out.

ANY HELP WILL BE MUCH APPRECIATED

Dear all experts:

I'm a college teacher and have been using Excel to post students' various quiz/exam grades on the web. However, starting from this semester, we were asked to respect students' privacy of NOT sending/posting the grades of the entire class anymore.

Since I usually have several hundreds of students' grades to take care of each semester, it really becomes a burden if I had to look up each student's class taken/grade/email address and send out individual email notices. Therefore, I'm wondering if there's any solution in Excel's macro or programming capability to automatically send each individual student's grade to him/her once I have their grades calculated in Excel?

What I have in mind is that I'll have the following info in an Excel file:

Cloumn A: Student's name
Column B: Student's ID
Column C: Student's email address
Column D: Name of the course
Column E: Name of the test (e.g. quiz #1, midterm exam, final exam, ...etc.)
Column F: grade or score
Column G: percentile for the preceding grade in the class
Column H: course coordinator's email address

After I'm done with the grading process, I wish to use my email app (MS Outlook 2007) to automatically send each student with their perspective grade info in the following format:

Dear [Column A]:
For the test Column E] of the course [Column D] you've registered, your grade is [column F] with a [Column G]% among your classmates. If you have any question on your grade, please contact the course coordinator at [Column H], thanks.


Since I'm only an average Excel user w/o any knowledge on how to compose any codes/macros, I wonder if this request is do-able withing Excel itself? I'll highly appreciate if I could get any help/suggestion from you. Many thanks in advance!

Best,
Marc

Hi,
I request for help from all about How to apply a Macro to do vlookup.

I can vlookup pretty comfortably but very often a single sheet would have in excess of 2500 entries and looking at my span of control I would be receiving anywhere close to 30-50 Sheets on a single day.So when I consolidate, things can go out of hand beyond a point of no-return. So I seek help.

The file that I am using has 2 sheets - "Project Tracking Sheet" and "Part Number".

Sheet 1 - Column F is titled Part Number and I propose to enter Part Numbers in there.
Sheet 2 has Description about those Part Number's.

All what I want is that when I run a Macro:-
a) A Whole New Column gets inserted between Column F and G, titled "Part Description".
b) Vlookup happens on Part Numbers stored under Column F and the corresponding Part Description Stored in Sheet2 gets entered in the New Column that was inserted.

Note:
1) While I have certain degree of command over Excel,I am a big-zero when it comes to VB/VBA etc.
So I can request that the entire code be pasted ..... please.
2) I have attached a blank file here for easy reference.
3) I did find another thread here for the same task here but as I said that I am Big-Zero when it comes to VB/A etc,it was of no help to me.

Anyhelp by anyone would be much appreciated.

I have to schedule a large number of students for remediation. I have some
students who have five study halls and some that have only one or two. I
need to find a way to create a remediation schedule that limits the number of
students that can come each period, but still gets each student to a
remediation period. I have already thought about naming each period of the
week as 11, 12, 13 (day 1, period 1; day 1, period 2; day1, period 3) up to
57, 58 (day 5, period 7; day 5, period 8). I can list the study halls that
each student has by using these numbers. If anyone has any suggestions as to
how to proceed so that I can schedule those students who have one study hall
before the students who also have the same study hall period but have other
study halls in which they could be scheduled, I would appreciate your help.

American Lit.

A B C D E F 1 Assignment Date Score Possible Percent Turned In 2 3 Journal - The Road Less Traveled 29-Oct-08 8 5 160% TRUE 4 Journal - Perserverance 28-Oct-08 7 5 140% TRUE 5 Profundity - Madame Loisel 28-Oct-08 #NA 21 #VALUE! #VALUE! 6 Journal - Trading Spaces 27-Oct-08 10 10 100% TRUE 7 Weekly Goal - 10/20/2008 24-Oct-08 #NA 5 #VALUE! #VALUE! 8 Journal - Virtual Husband 24-Oct-08 #NA 10 #VALUE! #VALUE! 9 Study Guide - The Necklace 24-Oct-08 #NA 40 #VALUE! #VALUE! 10 Plot Development - The Necklace 24-Oct-08 #NA 8 #VALUE! #VALUE! 11 Journal - A Father's Farewell 23-Oct-08 10 10 100% TRUE 12 Journal - Only Words 22-Oct-08 #NA 10 #VALUE! #VALUE! 13 Journal - How Do I Love Thee 21-Oct-08 6 5 120% TRUE 14 Journal - Who Packs Your Parachute? 20-Oct-08 0 10 0% FALSE 15 Journal - Dumped Teen Follow - up 16-Oct-08 8 10 80% TRUE 16 Weekly Goal - 10/13/2008 16-Oct-08 #NA 5 #VALUE! #VALUE! 17 Journal - Ode to the Champions 15-Oct-08 6 5 120% TRUE 18 Journal - Teen Dumped 14-Oct-08 10 10 100% TRUE 19 Journal - The Dash 13-Oct-08 8 10 80% TRUE 20 Weekly Goal - 10/10/2008 10-Oct-08 #NA 5 #VALUE! #VALUE! 21 Narrative Profundity - Della (Gift of the Magi) 10-Oct-08 0 21 0% FALSE 22 Compare/Contrast MDG Movie and Short Story 10-Oct-08 0 16 0% FALSE 23 Journal - Dog Days of School 10-Oct-08 #NA 10 #VALUE! #VALUE! 24 Journal - Learning to Love 9-Oct-08 7 10 70% TRUE 25 Journal - You Did Good 8-Oct-08 8 10 80% TRUE 26 Journal - Halloween 7-Oct-08 4 5 80% TRUE 27 Most Dangerous Game - Review 6-Oct-08 #NA 8 #VALUE! #VALUE! 28 Journal - Grammar Lesson 6-Oct-08 3 5 60% TRUE 29 Journal - Gratitude to Old Teachers 3-Oct-08 6 5 120% TRUE 30 Weekly Goal - 9/29/2008 3-Oct-08 #NA 5 #VALUE! #VALUE! 31 Journal - The Bagel 2-Oct-08 5 5 100% TRUE 32 Journal - Otherwise 1-Oct-08 0 5 0% FALSE 33 Compare/Contrast MDG and GI 28-Sep-08 13 15 87% TRUE 34 Journal - Shadows 26-Sep-08 5 5 100% TRUE 35 Journal - Advice from the Experts 25-Sep-08 5 5 100% TRUE 36 Plot Development Organizer - MDG 25-Sep-08 0 8 0% FALSE 37 Narrative Profundity - Zaroff 25-Sep-08 18 21 86% TRUE 38 Journal - The Meadow 25-Sep-08 5 5 100% TRUE 39 Narrative Profundity Chart - Rainsford 25-Sep-08 15 21 71% TRUE 40 Focus Question #1 - MDG 24-Sep-08 #NA 12 #VALUE! #VALUE! 41 Understanding Plot Development - MDG 24-Sep-08 0 16 0% FALSE 42 Weekly Goal - 9/22/2008 23-Sep-08 5 2 250% TRUE 45 Journal - Advice 18-Sep-08 5 5 100% TRUE 46 Journal - The Distances 17-Sep-08 E #NA - #VALUE! 47 Essential Questions 17-Sep-08 #NA 12 #VALUE! #VALUE! 48 Journal - Lines 16-Sep-08 E 5 - #VALUE! 49 Journal - The Blue Bowl 15-Sep-08 E 5 - #VALUE! 50 Weekly Goal - 09/15/2008 15-Sep-08 E 5 - #VALUE! 51 167 252 66% 6
Spreadsheet Formulas Cell Formula E3 =C3/D3 F3 =IF(C3,C3>1,C3>0) E4 =C4/D4 F4 =IF(C4,C4>1,C4>0) E5 =C5/D5 F5 =IF(C5,C5>1,C5>0) E6 =C6/D6 F6 =IF(C6,C6>1,C6>0) E7 =C7/D7 F7 =IF(C7,C7>1,C7>0) E8 =C8/D8 F8 =IF(C8,C8>1,C8>0) E9 =C9/D9 F9 =IF(C9,C9>1,C9>0) E10 =C10/D10 F10 =IF(C10,C10>1,C10>0) E11 =C11/D11 F11 =IF(C11,C11>1,C11>0) E12 =C12/D12 F12 =IF(C12,C12>1,C12>0) E13 =C13/D13 F13 =IF(C13,C13>1,C13>0) E14 =C14/D14 F14 =IF(C14,C14>1,C14>0) E15 =C15/D15 F15 =IF(C15,C15>1,C15>0) E16 =C16/D16 F16 =IF(C16,C16>1,C16>0) E17 =C17/D17 F17 =IF(C17,C17>1,C17>0) E18 =C18/D18 F18 =IF(C18,C18>1,C18>0) E19 =C19/D19 F19 =IF(C19,C19>1,C19>0) E20 =C20/D20 F20 =IF(C20,C20>1,C20>0) E21 =C21/D21 F21 =IF(C21,C21>1,C21>0) E22 =C22/D22 F22 =IF(C22,C22>1,C22>0) E23 =C23/D23 F23 =IF(C23,C23>1,C23>0) E24 =C24/D24 F24 =IF(C24,C24>1,C24>0) E25 =C25/D25 F25 =IF(C25,C25>1,C25>0) E26 =C26/D26 F26 =IF(C26,C26>1,C26>0) E27 =C27/D27 F27 =IF(C27,C27>1,C27>0) E28 =C28/D28 F28 =IF(C28,C28>1,C28>0) E29 =C29/D29 F29 =IF(C29,C29>1,C29>0) E30 =C30/D30 F30 =IF(C30,C30>1,C30>0) E31 =C31/D31 F31 =IF(C31,C31>1,C31>0) E32 =C32/D32 F32 =IF(C32,C32>1,C32>0) E33 =C33/D33 F33 =IF(C33,C33>1,C33>0) E34 =C34/D34 F34 =IF(C34,C34>1,C34>0) E35 =C35/D35 F35 =IF(C35,C35>1,C35>0) E36 =C36/D36 F36 =IF(C36,C36>1,C36>0) E37 =C37/D37 F37 =IF(C37,C37>1,C37>0) E38 =C38/D38 F38 =IF(C38,C38>1,C38>0) E39 =C39/D39 F39 =IF(C39,C39>1,C39>0) E40 =C40/D40 F40 =IF(C40,C40>1,C40>0) E41 =C41/D41 F41 =IF(C41,C41>1,C41>0) E42 =C42/D42 F42 =IF(C42,C42>1,C42>0) E45 =C45/D45 F45 =IF(C45,C45>1,C45>0) F46 =IF(C46,C46>1,C46>0) E47 =C47/D47 F47 =IF(C47,C47>1,C47>0) F48 =IF(C48,C48>1,C48>0) F49 =IF(C49,C49>1,C49>0) F50 =IF(C50,C50>1,C50>0) C51 =SUM(C3:C50)-20 D51 =D3+D4+D6+D11+D13+D14+D15+D17+D18+D19+D21+D22+D24+D25+D26+D28+D29+D31+D33+D34+D35+D37+D38+D39+D41+D42+D45 E51 =C51/D51 F51 =COUNTIF(F1:ORF50,"FALSE")

Working in Excel 2010:

I have looked at a lot of examples and have probably tried a 100 variations to get this to work, but to no avail.
Not sure what I'm not seeing, it would appear this should be simple. With everything I have tried, I have also
dealt with "sub-script out of range" and "Type mis-match" errors. I have spent days on this little issue. Sucks.

1. UserForm has a ComboBox and Label
2. ComboBox is populated by a Range, from the sheet, in the RowSource property (this works fine)
3. Sheet cells in column "A" are formatted as text, cells in column "B" are formatted as numbers
4. Named range of "Mtl" is defined, workbook wide
5. The Label is to display the vlookup value, this value will also be used in other calculations
6. When the Combobox does a change, the vlookup needs to upgate the Label and variable

?_tried passing different types of variables to vlookup, not sure whats best
?_tried calling the range 2 dozens different ways

Any help would be appreciated.

Thanks.

Hello, I have a slight problem.
I have data in a pivottable which I want to compare with another table.

The lastcolumn+1 of the pivottable needs to get data from another table.

I have this for an example:

Code:

Dim y As Range
Dim lookFor As Range
Dim rng As Range
Dim col As Integer
Dim found As Variant


Set rng1 = Sheets("Invulsheet engineer").Range("A:B")
col = 5
i = 7

For Each lookFor In Sheets("PivotLLI").Range.Column(D, i)
found = Application.VLookup(lookFor, rng1, col, False)
Set Sheet("PivotLLI").Range(mylastcolumn3) = found
i = i + 1
Next

Can anybody give me leads on how to solve this?

Would be appreciated!!


BONUS:
I defined my Lastcolumn succesfully:

Code:

Dim myLastRow2 As Long
Dim myLastColumn2 As Long
Dim mylastcolumn3 As Integer
Dim rng3 As Range

Range("A1").Select
    myLastRow2 = Cells.Find("*", [a1], , , xlByRows, xlPrevious).Row
    myLastColumn2 = Cells.Find("*", [a1], , , xlByColumns, xlPrevious).Column
    mylastcell2 = Cells(myLastRow2, myLastColumn2).Address
    myrange2 = "a1:" & mylastcell2
    mylastcolumn3 = myLastColumn2 + 1
ActiveSheet.Name = "PivotLLI"

Question: A professor at my University used a Macro to detect if a student's computer had multiple Excel files open at once, which he then said was cheating. However, when looking at the Excel files, there is a "mymacro" listed but it's locked down nice at tight. There is no way to look at it or edit it because it's password protected, but I was wondering is it possible that he used only a macro to do detect the cheating? And if so, is there any way besides getting password from him to see what exactly the code was?

Thanks for the help.

I have a standard vlookup formula.

Code:

VLOOKUP(A2,'Prefered Suppliers'!A$2:B$53,2,FALSE)

But the source list will eventually grow. But in the formula, the range is fixed. Is there a way via VBA to monitor the source so that when another row is added (row 54), the formula updates to reflect 54 instead of remaining at 53?