This article explains how the formulas in the sheet work.

So this workbook has some semi-complicated formulas as well as some simple Microsoft Visual Basic scripting. Below you will find an explanation of how each formula in the TEMPLATE sheet works and how we are able to get roster data from R numbers. There will also be a section outlining how the VBA scripting works and what all it does.

Parsing of the R# from the magstripe data:

So the first problem I came across was that the data on the RaiderCard magstripe is not just your R number. For example, if your R number was 12345678, when you swipe your RaiderCard, you would get ;12345678=0067?. the ; and =0067? appear to be the same across all RaiderCards (from what I’ve seen anyways). So I needed to figure out a way to get Excel to just get the R number and discard everything else. This was accomplished using the following Excel formula:

=IF(ISBLANK(A2), "",IF(ISNUMBER(A2), A2, INT(IF(ISBLANK(A2), "", RIGHT((LEFT(A2,SEARCH("=",A2)-1)),LEN((LEFT(A2,SEARCH("=",A2)-1)))-1)))))

There are several nested IF statements here, so lets break them down:

  • The first IF statement simple looks to see if the cell A2 is blank or not. It if is, then the cell this formula is in will have the value "", which means it will also be blank (this is to prevent formula errors and makes it look nicer!). If the cell is not blank, then we move onto the next IF statement.
    • This IF statement looks to see if the cell A2 is a number. If it is, then the cell this formula is in simply copies what is in cell A2. This allows students to enter their R numbers manually and the name and lab numbers can still populate. If A2 isn’t a number (which if they swipe their cards, Excel will see it as text) then we move onto the next IF statement.
      • Here we have an INT before the next IF statement. This forces the data we are about to get to be an integer in Excel. We have to do this because the data entered by reading a card will be seen as text in Excel, which causes problems for us later when we try and match R numbers to names. This fixes that issue.
      • This IF statement again looks to see if A2 is blank. I honestly do not remember why I did this… If A2 is not blank, then we move onto the next part of the formula (we’re out of the IF statements!)
        • Now we have the part that actually extracts the R number out of the data! To do this, we use RIGHT and LEFT. I’m honestly not super sure how exactly this works, so I hope it doesn’t break. The basis is that it gets the length and chops off the sides of the R number and then the cell the formula is in will be the value of the R number. I have yet to have an issue with it!

This was another tricky part I had to figure out! To do this, I initially tried using PowerQuery to query data from Azure Active Directory but unfortunately, Azure AD does not have R numbers, just names. I ended up getting the master lab roster, which includes names, lab course number, and R numbers (and some other data we don’t care about). This data is stored in a separate, hidden sheet titled Students. The lab roster data within this sheet is hidden to make sure we don’t accidentally show it or send it to someone.

Now how do do we actually FIND stuff??

We’re going to use the FIRST name column as an example. The LAST and LAB NUMBER columns follow an almost identical formula.

=IF(C2="", "", VLOOKUP(C2,Students!$A$2:$D$500,2,FALSE))

  • So first, we have an IF statement. This just looks to see if C2 in this case is empty (C2 is where our R number data is put from the part above!)
  • Next, we use a VLOOKUP command on C2 in the Students sheet on the cells ranging from A2 to D500. Note the dollar signs. This tells Excel to not change these cell values if this formula is moved (eg., copied somewhere else in the sheet). VLOOKUP is going to look in the leftmost column of our Students sheet and try and find a match for C2. Once it finds this match, it is going to return the value that is in the second column of our Students sheet. (hence the 2 in between $500 and $FALSE). If it cannot find a match, then it will return FALSE (which means that the R number was either entered incorrectly or that student is not in lab). Now when this happens, we can add people to the roster! Look at the following section to see how to do this:

The magic that is Visual Basic

The title is a lie. I hate Visual Basic. It was not fun to code this.

Anyways, here’s the VB code for the TEMPLATE sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
      
  ActiveCell.Offset(-1, 3).Activate

    If IsError(ActiveCell) Then
    UserForm1.TextBox1.Value = ActiveCell.Offset(0, -1)
    UserForm1.Show
    
    Else
        ActiveCell.Offset(1, -3).Select
    End If
end_prog:
End Sub

This code executes when a value is changed, and then will move around the active cell depending on if there is an error or not. If there IS an error, then it will show UserForm1, which will allow the user to enter a student’s name and lab number manually. This data will be added to the Students sheet so that the student who is not in lab doesn’t have to manually enter their information again. It should be noted that this scripting will only work on Excel for Windows when macros are enabled. It will not work on macOS or the web version of Excel.