Revisiting NES Classics Using Nintendo Switch Online Rewind Feature

Ghosts ‘n Goblins is the most difficult NES game I own. Battletoads is certainly challenging in spots like the Turbo Tunnel, but it doesn’t seem as punishing as Ghosts ‘n Goblins. As a kid, I spent hours (and hours) trying to make my way through this game. It requires repeated grinding through every inch of every stage to learn where to move in order to defeat enemies coming from every direction. Simply finishing the initial graveyard stage feels like a great accomplishment.

Ghosts 'n Goblins - Box Art
Ghosts ‘n Goblins – Box Art

The level design has enemies in the worst possible spots. The game mechanics make the Knight very hard to maneuver while the enemies are able to move freely and at high speed. The game also suffers from slowdown if too much is happening on screen at the same time (which seems to happen most of the time). According to the box art, this represented “state-of-the-art” and “high resolution graphics”. One hit causes the Knight to lose his armor and proceed only in his skin and red underwear. A second hit makes you a pile of bones.

Ghosts 'n Goblins - Pile of Bones
Ghosts ‘n Goblins – Pile of Bones

Any enjoyment from this intense challenge eventually turned into complete frustration. After spending hard-earned dollars to buy the game, it was disappointing to admit defeat and put the game cartridge away to collect dust.

Many years later, I signed up for the Nintendo Switch Online primarily to play Super Mario Bros. and The Legend of Zelda without having to dig out my old NES and some crusty controllers. As I looked through the catalogue of classic NES games, Ghosts ‘n Goblins caught my attention. Maybe it wasn’t as difficult as I remembered. Maybe my skills improved during those intervening years.

No, it is still impossible. At this point, I couldn’t even make it through the first stage. I was about to put the game back on the digital shelf to collect virtual dust.

Instead, I found that all of the classic games in the Nintendo Switch Online library have a “rewind” feature. If the Knight is hit and down to his red underwear, I can rewind the game to any point up to a minute or so earlier. Is it cheating? Perhaps – but it certainly makes the game more enjoyable. I still have to make all of the jumps and get through the enemy hordes, but the ability to rewind removes most of the frustration. I don’t have the hours (and hours) to dedicate to this game as I had as a kid so this feature allows me to continue making progress without starting over after each mistimed jump or the flying attacks from the Red Devil.

With the rewind feature, I was able to make it to the end of the game after so many years…only to discover that you have to play through the whole thing a second time to get to the real ending!

Ghosts 'n Goblins - Nintendo Switch Rewind
Ghosts ‘n Goblins – Nintendo Switch Rewind

Creating Cascading Drop-down Lists in Excel

If you’re using Microsoft Excel to capture and track data, one of the challenges is maintaining good data quality when more than one person is updating the workbook. The data validation features in Excel help by only allowing the user to select data based on pre-defined options in a list. This feature works well on individual cells. However, if you have a column that depends on the value in a different column, you will need to get a little more creative. This post describes the steps for creating cascading drop-down lists in Excel using a combination of data validation and named ranges.

Step 1 – Define the Reference Data

Let’s assume that we are collecting data that includes Organization and Department attributes. In this example, an Organization is the parent to one or more Departments (children). We’ll set up the reference data for the data validation drop-down lists as follows on its own worksheet.

  • Column A (“ORGANIZATIONS”) represents the list of valid Organizations to appear in the Organization drop-down list.
  • Column B (“ORGANIZATION TO DEPARTMENT MAPPINGS”) represents the names given to ranges that will be defined in a later step.
  • Columns D through E (“REF_ORGANIZATION_X_DEPARTMENTS”) represent the lists of valid Departments to appear in the Department drop-down list depending upon which Organization is selected.
Excel Cascading Drop-down List - Reference Data
Reference Data

Step 2 – Define the Named Ranges

Now that we have the reference data established, we can define the named ranges that refer to these lists. The named range entry defined as REF_ORGANIZATION_TO_DEPARTMENT_MAPPINGS is the key to creating the cascading drop-down list functionality. It is important that the values in Column B (above) match the names given to the REF_ORGANIZATION_X_DEPARTMENTS ranges. This value is used in a subsequent step using a combination of INDIRECT and VLOOKUP in the data validation formula.

Excel Cascading Drop-down List - Named Ranges
Named Ranges

Step 3 – Define the Organization Data Validation (Parent Column)

In this step, we establish the standard data validation on the parent data in Column A (“REF_ORGANIZATIONS”) on a new data collection worksheet.

Excel Cascading Drop-down List - Parent Level Validation
Parent Level Validation

Step 4 – Define the Department Data Validation (Child / Dependent Column)

Here we establish the data validation rule that performs the cascading drop-down list function. Once the user selects a valid Organization, this formula performs a VLOOKUP against the REF_ORGANIZATION_TO_DEPARTMENT_MAPPINGS named range and returns the name of the Department named range, e.g. REF_ORGANIZATION_X_DEPARTMENTS, associated with the selected Organization. The indirect function then converts that name, a text value, into the named range reference.

=INDIRECT(VLOOKUP(A2,REF_ORGANIZATION_TO_DEPARTMENT_MAPPINGS,2,FALSE))
Excel Cascading Drop-down List - Child Level Validation
Child Level Validation

When you click the OK button, Excel will display the following error message. Click the Yes button to continue.

Excel Cascading Drop-down List - Validation Error Message
Validation Error Message

Step 5 – Test the Cascading Drop-down Lists

Now that the data validation rules are set up, we can test the cascading drop-down list functionality. Selecting “Organization 1” causes the validation drop-down list in the Department column to reflect only those Departments associated with “Organization 1”. Selecting “Organization 2” causes the Department drop-down list to show those Departments associated with “Organization 2”.

Excel Cascading Drop-down List - Parent List Values
Parent List Values
Excel Cascading Drop-down List - Child List Values Based on Parent Selection
Child List Values Based on Parent Selection
Excel Cascading Drop-down List - Both Parent and Child Selected
Both Parent and Child Selected

Step 6 – Macro to Validate Parent / Dependent Relationship

Now that the worksheet is functioning as expected, you release it for users to update and someone will inevitably enter data in a way that breaks the parent / child relationship. As an example, “Organization 2” is selected in the Organization column and “Department 6” is selected in the Department column. The user then returns to the Organization column and changes the entry to “Organization 3”. The Department column retains the value of “Department 6” which is not valid for the “Organization 3” selection in the Organization column. To help avoid these errors, you can save the file as a macro enabled workbook and add the following code to the data entry worksheet. If this macro had been enabled, the “Department 6” value is removed as soon as the user selected “Organization 3”. This code assumes that the parent column is the first column (Column A) in the worksheet and the child column is immediately to the right (Column B). Please revise the code to meet your specific requirements.

Excel Cascading Drop-down List - Selecting Parent Field Clears Child Field Through Macro
Selecting Parent Field Clears Child Field Through Macro

Example

Excel Cascading Drop-down List – Example in Action
Excel Cascading Drop-down Lists in Action

Source Code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    If Target.Column = 1 Then
        If Target.Validation.Type = xlValidateList Then
            If Not Target.Offset(0, 1).Validation.Value Then
                Target.Offset(0, 1).ClearContents
            End If
        End If
    End If
exitHandler:
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

Replace Line Feeds With Commas Using Javascript

The following JavaScript function can be used to replace line feeds and carriage returns with commas. I find this particularly useful when formatting a data column from Excel into an SQL constraint.

Sample Input

1
2
3
4
5

Sample Result

1,2,3,4,5

The JavaScript function uses a simple regular expression to remove line feeds and carriage returns. The comma as the second argument to the ref.value.replace function may be replaced with any other character to meet your specific requirement.

Live Test

Test the code using the following text area. Enter a carriage return / line feed separated column of data, move focus off the text area, and the code will automatically format the list.

Source Code

function remove_crlf(ref) {
  ref.value = ref.value.replace(new RegExp("[\r\n]", "gm"), ",");
}

Using a simple HTML page with a Text Area, we can paste the data list into the form. When the cursor is moved away from the Text Area, the function is automatically called and the list is formatted with commas replacing the line feeds and carriage returns on each line.

Putting all of the code together, we get the following which can be saved to an html file.

<html>
  <head>
    <script language='javascript'>
      function remove_crlf(ref) {
        ref.value = ref.value.replace(new RegExp("[\r\n]", "gm"), ",");
      }
    </script>
  </head>
  <body>
    <form name='frmremover'>
      <textarea name="txtdata" rows="5" cols="40" onblur="javascript:remove_crlf(this)"></textarea>
    </form>
  </body>
</html>