|
Size: 2503
Comment:
|
Size: 2898
Comment:
|
| Deletions are marked like this. | Additions are marked like this. |
| Line 1: | Line 1: |
| #acl FacultyGroup:admin,read,write,delete,revert All:read |
|
| Line 3: | Line 5: |
| The scenario is that you have a spreadsheet in a format specific to your course that includes student IDs, grades, and perhaps other information. One way to submit these grades is to export a grades spreadsheet from Learn@UW, copy the grades from your spreadsheet into the designated column in the Learn@UW spreadsheet, and import that spreadsheet into Learn@UW. | This tip for submitting grades applies to the following scenario: You have a spreadsheet in a format specific to your course that includes student IDs, grades, and perhaps other information. One way to submit these grades is to export a grades spreadsheet from Learn@UW, copy the grades from your spreadsheet into the designated column in the Learn@UW spreadsheet, and import that spreadsheet into Learn@UW. |
| Line 5: | Line 7: |
| The procedure below shows how to use Excel's vlookup function to copy the grades from your spreadsheet into the Learn@UW spreadsheet. | The procedure below shows how to use Excel's vlookup function to automatically copy the grades from your spreadsheet into the Learn@UW spreadsheet. |
| Line 15: | Line 17: |
| Note that the two sheets do not need to be sorted in the same way. |
|
| Line 16: | Line 20: |
The vlookup function is used in a new column (here column N) in the Learn@UW sheet to get the grade from the grade sheet. |
|
| Line 21: | Line 27: |
| * The name of the cell in the Learn@UW sheet containing the student ID to match. | 1. The name of the cell in the Learn@UW sheet containing the student ID to match. |
| Line 23: | Line 29: |
| * The range of columns in the class grade sheet that contains the student ID and the grades. It doesn't matter if additional columns are included in this range. The first column in this range must be the student ID column. One way to enter the range in this column is by navigating to the other sheet and dragging the mouse over the column headers. | 1. The range of columns in the class grade sheet that contains the student ID and the grades. It doesn't matter if additional columns are included in this range. The first column in this range must be the student ID column. One way to enter the range in this column is by navigating to the other sheet and dragging the mouse over the column headers. |
| Line 25: | Line 31: |
| * The index of the column within the selected range that contains the grade in the class grade sheet. The first column in the range is index 1, the second is index 2, and so on. | 1. The index of the column within the selected range that contains the grade in the class grade sheet. The first column in the range is index 1, the second is index 2, and so on. |
| Line 27: | Line 33: |
| * Whether to do partial matches. Enter FALSE to get exact matches. | 1. Whether to do partial matches. Enter FALSE to get exact matches. |
| Line 35: | Line 41: |
| One way to do this is to hold the mouse over the lower right corner of the cell. The mouse pointer will become a plus. Double-click. The cell should get copied to the whole column. | One way to do this is to hold the mouse over the lower right corner of the cell. The mouse pointer will become a plus. If you double-click, the cell should get copied to the whole column. |
| Line 39: | Line 45: |
| == Copy the '''values''' of the vlookup cells into the grades column == | == Copy the values of the vlookup cells into the grades column == |
| Line 41: | Line 47: |
| Copy the cells and then use "Paste Special" and choose to paste the values. | Copy the cells and then use "Paste Special" and choose to paste the values. We do not want to copy the formulas, just the values. |
| Line 55: | Line 61: |
| In rare cases, an error may reported about the roster not matching. In this case, it may be necessary to import section by section. | In rare cases, an error may be reported during import about the roster not matching. In this case, it may be necessary to import section by section. |
Using Excel's vlookup function to copy grades into a Learn@UW spreadsheet
This tip for submitting grades applies to the following scenario: You have a spreadsheet in a format specific to your course that includes student IDs, grades, and perhaps other information. One way to submit these grades is to export a grades spreadsheet from Learn@UW, copy the grades from your spreadsheet into the designated column in the Learn@UW spreadsheet, and import that spreadsheet into Learn@UW.
The procedure below shows how to use Excel's vlookup function to automatically copy the grades from your spreadsheet into the Learn@UW spreadsheet.
Example class grade sheet
Example Learn@UW sheet
Note that the two sheets do not need to be sorted in the same way.
Use vlookup to copy grades from the class grade sheet into the Learn@UW sheet
The vlookup function is used in a new column (here column N) in the Learn@UW sheet to get the grade from the grade sheet.
The arguments to vlookup are:
- The name of the cell in the Learn@UW sheet containing the student ID to match.
- The range of columns in the class grade sheet that contains the student ID and the grades. It doesn't matter if additional columns are included in this range. The first column in this range must be the student ID column. One way to enter the range in this column is by navigating to the other sheet and dragging the mouse over the column headers.
- The index of the column within the selected range that contains the grade in the class grade sheet. The first column in the range is index 1, the second is index 2, and so on.
- Whether to do partial matches. Enter FALSE to get exact matches.
After entering the vlookup formula, pressing Enter should show the correct grade in the cell.
Copy the vlookup cell to the rest in the column
One way to do this is to hold the mouse over the lower right corner of the cell. The mouse pointer will become a plus. If you double-click, the cell should get copied to the whole column.
Copy the values of the vlookup cells into the grades column
Copy the cells and then use "Paste Special" and choose to paste the values. We do not want to copy the formulas, just the values.
The result:
Clear the vlookup column, now that it is no longer needed
Import the completed sheet into Learn@UW
In rare cases, an error may be reported during import about the roster not matching. In this case, it may be necessary to import section by section.