Shift down content of range of row cells in Sheet 1 when new

Hello edge37 and welcome to the forum.

You did a great job of pasting your screen shots into an Excel file to clearly explain what you want to achieve. (You understood the forum rules that only Excel files can be posted)

I created a file using your screen shots for reference and simple macros to get the job done. There are two sheets: "Grades" and "Students". To see how it works go to the "Students" sheet and add one or more names in col "C"; then click the "Add New Students" button.

Update Dec. 2/23 - see discussion below

There are 3 steps to the code: 1) add the new name(s) to the master list on the "Students" sheet and resort the list; 2) add the new names to the "Grades" sheet and resort the list; 3) clear the names in col "C" of the "Students" sheet.

If this solves your problem, please mark my answer as Selected.

CHeers   :-)

Hi Michael

There's no easy way to do what you want using just a formula so I too used VBA (Excel's built-in programming language) to do that.

My code relies on "events"- when something happens in Excel, it can trigger an event.(and there are several types).

I've recreated your sheets in the attached file. If you open it (with macros enabled - check the internet if you don't know how),and add a row to Sheet1, a new row will be added and "push down" the lower rows in Sheet2. If you correct a name (say you had a spelling error), that will be mirrored in Sheet2. If you delete a row containing a name (or several), you'll get a warning message to manually delete in Sheet2. 

How does it work? Firstly, Sheet 2 contains the data in a table, where the formula in cell A2 is:

=ROW()-1

so B2 shows 1 (its row number 2 minus 1 =1) and luckily the table just fills that down the column automatically and adjust as rows are added/ deleted lower down

Behind Sheet1 are two event procedures. The first is this (where the bits in red - following the apostrophe ' - don't do anything but are comments to tell you what happens in the next lines):

Private NumRws As Long

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'record rows before the change is made
    NumRws = CLng(ActiveSheet.UsedRange.Rows.Count)
End Sub

The bit in bold "declares" a variable used to detect if any rows were deleted (in the first section of the code below). 

The main code is triggered when things change in Sheet1 only. Here's that code

Private Sub Worksheet_Change(ByVal Target As Range)
    ' warn if used rows were deleted
    If NumRws > ActiveSheet.UsedRange.Rows.Count Then
        MsgBox "Row(s)deleted- please delete same row(s) on Sheet 2"
        Exit Sub
    End If

    ' otherwise do nothing unless a single cell in A is changed
    If Intersect(Target, Columns(1)) Is Nothing Or Target.Count > 1 Then Exit Sub

    ' stop this code re-triggering itself
    Application.EnableEvents = False

    ' get the new (or revised) name
    NewName = Target.Value

    If Target.Value = "" Then Exit Sub
    ' see if it's a fresh name by reversing the change
    Application.Undo
    ' then checking what it was
    If Target.Value = "" Then
        ' if it was nothing, add a fresh row at the same place in Sheet2
        Sheet2.Cells(Target.Row, 2).EntireRow.Insert
        ' and add value in B of the inserted row
        Sheet2.Cells(Target.Row, 2).Value = NewName

        Else
        ' if text changed, write the new name in that row
        Sheet2.Cells(Target.Row, 2) = NewName
    End If

    ' restore the changed value
    Target.Value = NewName
    ' allow events
    Application.EnableEvents = True
End Sub

Note that it assumes that you don't add rows to Sheet2 manually but do delete those rows deleted on Sheet1 (to keep the names/ rows aligned between sheets). 

Hopefully this works well and automatically for you. If so, please remember to mark this Answer as Selected (or do the same for Willie's Answer if you prefer his).