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 :

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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