Search code examples
excelformulaspreadsheetnamed-ranges

Excel named range formula, for cell sequence

I'm trying to set up a sheet that will input data in a specific cell order. Basically I need the first enter to go to the next column, and then the second enter to go to the previous column but one row down.

Example image

I linked a picture to elaborate. I thought it would be possible with just defining a named range and selecting each cell individually, but I need for this to work for 500+ rows. It's my understanding you can use a formula to define a named range, but I'm having difficulty figuring out how to do that.

edit: More elaboration on the input method. In this case I can only use the enter key due to the input device. This sheet also has to be shared with people, so setup instructions are not ideal.

Solution

  • Based on the question as it stands (and without further qualification), the following code will do what you want:

    Option Explicit

Private Sub Worksheet_Change(ByVal rgTarget As Range)
    
    With rgTarget
        Select Case .Column
            Case 1: .Offset(0, 1).Select
            Case 2: .Offset(1, -1).Select
        End Select
    End With
    
End Sub

    The above goes into the VB of the Sheet where the data entry will occur. If you don't know what that means, let me know and I'll provide details on how to implement.