Refer to Sheets in Formula without INDERECT

Dear all,

I have a workbook which contains 400 sheets. The sheets are updated every day. On the first sheet there is a "Main" sheet were i'm trying to build a matrix which counts certain things from every sheet. In the martix I want to make a formula which refers to a the corresponding sheet. I have a list of words (the names of the sheets) on the "Main" sheet were I can reference it to. I got it to work with the INDIRECT function, but is was very very slow. Does anyone have a good idea how to make is faster, perhaps without INDIRECT? I don't mind using VBA.

Thanks in advance!
PS: I have attached a small printscreen of my problem.


Capture.PNG

I've assumed that the formula was for illustration only, and doesn't actually make sense.

Have you actually established that the performance degradation was because you introduced INDIRECT? If so, then you can use a helper macro to enter literal (non-INDIRECT) formulas from a column of sheet names.

Here's one way:-

no_indirect_cy.xlsm