CODEWITHSUNDEEP
excelformulaspreadsheetnamed-ranges
Al01
Al01

Reputation: 15

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.

Upvotes: 0

Views: 193

Answers (2)

Spinner
Spinner

Reputation: 1088

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.

Upvotes: 0

teylyn
teylyn

Reputation: 35970

Excel recognises data entry patterns when data is entered manually.

  • enter something into A1 and press Tab to confirm and go to B1
  • enter something into B1 and press Enter.
  • now the active cell is A2
  • enter into A2 and press Tab to go to B2
  • enter into B2 and press Enter to go to A3
  • etc.

No need to set up anything special or create named ranges for that.

Upvotes: 0

Related Questions