Student+Logins+-+Generating+Class+Lists

It's incredibly helpful to be able to generate a list of student usernames and passwords for teachers who will be taking students to computer labs, signing out laptop carts, etc. //This can save teachers all kinds of frustration, especially at the beginning of the year. This can also save Liaisons time and the school money--frustrated kidlets can be a bit destructive...//

Generate a class list with all usernames and passwords

 * Before you Start**, you will need
 * Tech Liaison assistance to access and to export from DOT student login lookup website.
 * Ability to create New Variable Reports in STARS. //Your Principal should be able to assist with this.//
 * Intermediate/Advanced knowledge of Microsoft Excel //or really good "following directions" skills.//

> **=VLOOKUP(TRIM(B5),Student_AD_Accounts!F$2:G$1660,2,FALSE)** >> //For some reason, spaces appear in STARS's AccountIDs--the **TRIM** function is necessary to eliminate them--see FALSE explanation, below.// >> //The "**$**" simply locks the first and last cell so that when you fill down the formula--see step 10--the reference array does not also shift with the lookup value.//
 * 1) **Export** a list of all student usernames and passwords in your building from the Department of Technology site. //Directions for Tech Liaisons are [|here]--authorized BCPS login required for access.//
 * 2) In **STARS**, create a **New Variable Report**, called "[|Student Logins, Class List]" with the following Report Fields:
 * //from "Student Demographics" category,// **LastFirst**
 * //from "Student Demographics" category,// **AccountID**
 * //from "Current Schedule" category,// **CourseNo**
 * //from "Current Schedule" category,// **ClassSection**
 * //from "Current Schedule" category,// **TeacherFull**
 * 1) Add the following Selection Criteria to that Variable Report: **CurrentSchedule.TeacherFull contains TEACHER**
 * //where// TEACHER is the full or partial name of the individual for whom you are creating the list of student logins
 * 1) Configure the following Sort On: **CourseNo**, then **ClassSection** (//check the "New Page" checkbox)//, then **LastFirst**
 * 2) Change the Format Output by selecting the **MS Excel** radio button. //Leave the "Save Report settings for later use" checkbox checked.// Click the **Print** button, and **Save** the exported file.
 * TROUBLESHOOTING (disappearing Excel exports in Internet Explorer): If the export window appears, loads, and then disappears, try printing the report a second time; however, before clicking the "Print" button, hold down the "Ctrl" key on your keyboard. Keep holding down the Ctrl key until prompted to Open/Save the report. //No idea why this happens or works...newly imaged machines print reports to Excel just fine, and then after a while, start failing, but the Ctrl solution works--thoughts?//
 * 1) **Open** both exported Excel files. **Move/Copy** the worksheets to a single Excel workbook.
 * 2) It's easiest to work from the **STARS export file**. Select that tab in your workbook.
 * 3) Insert a **new column** to the right of the AccountID, and give it the header **Password**.
 * 4) In the cell to the right of the first student's name in your new Password column (//Because STARS adds three rows of junk header in addition to the regular column headers, this is cell **C5**)//, lookup the student's password using the following formula:
 * //where// **B5** is the cell containing the first student's AccountID on the STARS export worksheet
 * //where// **Student_AD_Accounts** is the name of the worksheet exported from the Department of Technology [DOT] student login lookup website
 * //where// **F2:G1660** is the range (//two columns)// of data on the DOT student login export worksheet with all StudentIDs in the first column, and all Passwords in the second column.
 * //where// **2** is the number of the column in the reference array (//selected from DOT student login export worksheet//) which contains students' passwords
 * //where// **FALSE** allows for error messages (//rather than junk data)// to be returned if the AccountID is not found __exactly__ in the DOT student login worksheet as it appears in the STARS export worksheet. //If the TRIM function is not used on the lookup value, because of trailing and/or leading spaces in STARS, many "#N/A" errors will be returned.//
 * 1) **Fill Down** the formula to propagate the lookup to the rest of the students in the STARS export worksheet.
 * 2) **Print** for or **email** to the teacher.
 * **PRINT suggestion**: //If the Sort On settings (see #4 above) were configured properly, each class should appear on a separate page.// To **copy the first four Header rows** on the STARS export worksheet (//now with passwords)// to a separate page when printing,
 * From "**__F__ile**," select "**Page Set__u__p...**"
 * In "**Sheet**" tab, next to "__R__ows to repeat at top:" enter **$1:$4**, and click "**OK**"
 * **EMAIL suggestion:** //To avoid confusion, and for some semblance of security (weak, I know we're talking about closing the barn door after the chickens have already started fiesta-ing, but...),// **save as a CSV file** //(this removes the reference worksheet with all students' logins and passwords and saves only the values appropriate for that teachers' students, no formulas)//.