Autosort then #REF! issue on another sheet. Lock formulas and mirror cells?

[noveske]

This one feels real stupid. Thought about even just working in a script to copy and paste formulas back in between other scripts.

Sheet 1 - As soon as value are input, the sheet is set to auto update, sort and organize the data. Delete duplicates.
Sheet 2 - Cells reference cells from Sheet 1. Some data is modified by formulas.

=TRIM(LEFT(Input!E2,2))

=IFERROR(RIGHT(SUBSTITUTE(Input!C2," ","|",LEN(Input!C2)-LEN(SUBSTITUTE(Input!C2," ",""))),LEN(SUBSTITUTE(Input!C2," ","|",LEN(Input!C2)-LEN(SUBSTITUTE(Input!C2," ",""))))-FIND("|",SUBSTITUTE(Input!C2," ","|",LEN(Input!C2)-LEN(SUBSTITUTE(Input!C2," ",""))))),0)

When duplicates are deleted, since Sheet 2 just references Sheet 1, the deleted lines just become #REF! errors.

Is there a way so that the cells on Sheet 2 will lock to always reference what's on Sheet 1? Not update or change?
Just need it to mirror without ever moving.

 

[Ottsel]

do you have a visual by chance?

 

[noveske]

do you have a visual by chance?

So this is the data that is being input to the Sheet1. I want to keep it automatic. Not have to run a script every time.

Sheet1 automatically sorts by location, highlights duplicate "Numbers" then deletes duplicate "Numbers". When there's cells that are referenced, sorting moves them around to fit the sorting order. So this sheet works leaving only unique values and no repeats. I'm only focused on the "Number" Column as the location and tracking are irrelevant for my requirements.

Sheet2 just references to Sheet1's cells. You can see from the initial data, the #REF! errors are in the rows where the "Number" was a duplicate value since Sheet1 will sort and delete. Formulas are still intact and in order. Just the error rows are just there and don't break resulting in the data I'm looking for.

The wanted result is:

After thinking about this, I ended up figuring out a work around. Might be the wrong way and I'm always trying to learn if there's a more efficient or better way.
Since sorting will always have a value in the first cell, I figured I would just autofill the sheet on every update.

Sub AutoFill()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Roster")

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ws.Range("A2:D" & lastRow).FillDown
    
End Sub

Since sorting and deleting of duplicates breaks Sheet2. I added the AutoFill Script as a module. Then just added it to run at the end of organizing Sheet1.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyRange As Range
    Dim DataRange As Range
    Dim lastRow As Long
    Dim cell As Range
    Dim dict As Object
    Dim matchRow As Variant
    

    Set DataRange = Me.Range("A1:H100" & Me.Cells(Me.Rows.Count, "A").End(xlUp).Row)
    

    Set KeyRange = DataRange

    If Not Application.Intersect(Target, DataRange) Is Nothing Then
        Application.EnableEvents = False ' Disable event handling to prevent triggering another change event
        

        DataRange.Sort Key1:=KeyRange.Cells(1, 5), Order1:=xlAscending, Header:=xlYes
        

        Set dict = CreateObject("Scripting.Dictionary")
        

        For Each cell In Me.Range("B2:B100")
            If cell.Value <> "" Then ' Check for non-blank cells
                If dict.Exists(cell.Value) Then
                    dict(cell.Value) = dict(cell.Value) + 1
                Else
                    dict(cell.Value) = 1
                End If
            End If
        Next cell
        

        For Each cell In Me.Range("B2:B100")
            If cell.Value <> "" And dict(cell.Value) > 1 Then
                matchRow = Application.Match(cell.Value, Me.Range("B2:B100"), 0)
                If Not IsError(matchRow) And cell.Row <> matchRow + 1 Then
                    cell.EntireRow.Range("A1:H1").Interior.Color = RGB(255, 255, 0) 
                Else
                    cell.EntireRow.Range("A1:H1").Interior.ColorIndex = xlNone 
                End If
            End If
        Next cell
        

        Dim i As Long
        For i = DataRange.Rows.Count To 2 Step -1
            If DataRange.Cells(i, 1).EntireRow.Range("A1:H1").Interior.Color = RGB(255, 255, 0) Then
                DataRange.Cells(i, 1).EntireRow.Delete
            End If
        Next i
        
        Call Format
        Call AutoFill
        
        Application.EnableEvents = True ' Re-enable event handling
    End If
    
    
End Sub