The sequence for inserting the scores from QM Perception tests into Blackboard’s Grade Centre has three steps:
- Export the Grade Centre for your course into an Excel spreadsheet;
- Create a new column in that exported spreadsheet for your QMP test and paste the scores into it;
- Import the revised spreadsheet into Grade Centre.
Steps 1 and 3 are covered in detail within our Blackboard web pages:
The general approach for Step 2 – namely, copying scores from QMP test then pasting them into a new column in the exported Grade Centre spreadsheet – starts with having, in the same spreadsheet, all the relevant QM Perception results in one worksheet and the exported Blackboard Grade Centre in another worksheet. Sort both worksheets by student name, which make it easier to compare them, and then create a new column for your test in the Grade Centre worksheet and give it a suitable name, e.g. ‘Online test’.
- If you have a small group, say 50 students or less, it is often simplest to select the ‘source’ scores from the QMP worksheet for a group of students using your mouse and then paste them into the relevant ‘target’ column.
- If, on the other hand, you have a large group – or if the two worksheets are difficult to compare due to mismatched student lists, which can happen if students have not been added or removed in Banner, or if there are student absences on the day of the test – then it may be easier to use Excel’s VLOOKUP function to copy the scores over. This may also reduce the risk of transcription errors.
Copying scores from QMP test into an exported Grade Centre spreadsheet using vlookup
There are four steps in compiling a VLOOKUP statement which will do the job, which are listed below. Here is an anonymised screenshot depicting the target (‘Bb’) worksheet, with the ‘copied values column’ (AB2) highlighted in yellow:
(1) in the target worksheet: (‘Bb’ ) Click on the first cell in the column that will contain the copied values (AB2) and use VLOOKUP’s lookup_value argument to identify student usernames in the target worksheet:
(2) next, use the table_array argument to specify a range of cells to search in the source worksheet (‘Basic Information QMP’) – this will of course be all the rows and as many columns as necessary:
(3) next, specify in the col_index_num argument which column in the source worksheet to use for the looked-up value – in our example, column 6 in the QMP worksheet:
(4) finally, use the range_lookup parameter to ensure that looked-up values are only copied across to the target worksheet if the usernames are an EXACT match: