Alternative to INDIRECT for dynamic worksheet references

When you make a change in column A, a formula will be inserted/updated in that row of column B.

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim Cell As Range
   
   For Each Cell In Target
   
      If Cell.Column = 1 Then
         Cells(Cell.Row, "B").Formula = "=INDEX('" & Cell & "'!A:B,MATCH(""Data"",'" & Cell & "'!B:B,0),1)"
      End If
   
   Next Cell

End Sub