Alternative to using INDIRECT for dynamic references to other sheets

I've attached a sheet which shows the formulas in one row (of about 100) of a worksheet in a fairly large workbook. This particular sheet culls and organizes the data in the numbered worksheets (about 70 of them).

I'd like to make the workbook more efficient (as it hangs and crashes), so I'm assuming one of the culprits is my liberal use of INDIRECT, which refers to the numbered worksheets in order to get various cell values in them.

Is there a solution for me to use a non-volatile function for this? The worksheet numbers do change from time to time as I add data to my project, if this matters.

Thanks for your assistance.

Having 7,000 cells with INDIRECT could possibly slow things down, although it shouldn't be crashing. There is no non-volatile function (that I know of) that can reference different worksheets dynamically.

One solution would be using VBA to populate and subsequently refresh the formulas. The VBA could be set up to run only if something changes in row 9, the list of sheet names.

By the way, your title does not really describe your problem. You are asking to use INDEX and MATCH, assuming that's the solution, but it probably isn't. I would recommend:

Alternative to using INDIRECT for dynamic references to other sheets

Here is the work sheet event . When B column value is changed ,in the same row all formulas are changed automatically. Indirect not required. Hence problem will not be there.
In the initial stage to change all rows formulas. Cut the range say B10:B100. Paste the same in different column. Column B is blank now . Now paste back the data to B column. All rows formulas are now changed.
Code :

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("B10:B100"), Target) Is Nothing Then

Dim cel As Range
Dim val
Dim Ro As Long

Application.EnableEvents = False
Application.DisplayAlerts = False

For Each cel In Target
If cel <> "" Then
val = cel.Value
Ro = cel.Row
Cells(Ro, "C").Formula = "=IFERROR('" & val & "'!B5,"""")"
Cells(Ro, "D").Formula = "=IFERROR('" & val & "'!B15,"""")"
Cells(Ro, "E").Formula = "=IFERROR('" & val & "'!B12,"""")"
Cells(Ro, "F").Formula = "=IFERROR('" & val & "'!Z2,"""")"
Cells(Ro, "G").Formula = "=IFERROR('" & val & "'!W1,"""")"
End If
Next cel
Application.EnableEvents = True
Application.DisplayAlerts = True

End If
End Sub

Worksheetevent

To paste the code
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Paste the code
Close the VB window.
Save the file

I did not see number of pages as 70.
To convert formulas in all sheets macro code.

Sub FormulaChangeAll()

Dim val
Dim T As Long, LR As Long
Dim Sh As Worksheet

Application.EnableEvents = False
Application.DisplayAlerts = False

For Each Sh In Worksheets
With Sh
LR = .Range("B" & Rows.Count).End(xlUp).Row

For T = 10 To LR
val = .Cells(T, "B").Value
'.Range("C" & T).Formula = "=2*5"
.Cells(T, "C").Formula = "=IFERROR('" & val & "'!B5,"""")"
.Cells(T, "D").Formula = "=IFERROR('" & val & "'!B15,"""")"
.Cells(T, "E").Formula = "=IFERROR('" & val & "'!B12,"""")"
.Cells(T, "F").Formula = "=IFERROR('" & val & "'!Z2,"""")"
.Cells(T, "G").Formula = "=IFERROR('" & val & "'!W1,"""")"
Next T
End With
LR = 0
Next Sh

Application.EnableEvents = True
Application.DisplayAlerts = True


End Sub

Worksheet event is also required to change formulas as and when column B value changed.