Big File Using a lot of INDIRECT formulas

I am building a file which is highly sensitive, at the end of my build it will have over 200 sheetse. In some of the sheets I use alot of INDIRECT formulas and I have read that these are highly volatile and makes the file super slow.

Here is a sample of a formula that is in one of my sheets.

=INDEX(INDIRECT("'"&$B9&"'!e20:q130"),MATCH($A9,INDIRECT("'"&$B9&"'!A20:A100"),0),MATCH(K$7,INDIRECT("'"&$B9&"'!e26:q26"),0))

Can anyone offer any help with perhaps a different formula that is a bit quicker so I can reference specific cells in sheets?

Perhaps if I change this now in my file I can save myself headaches down the road.

Much appreciated.

I've worked with an Excel workbook with fewer worksheets but over 50K cells with formulas calling INDIRECT. It's quite slow.

Referring to the worksheet containing the cell with your formula as x, given the form of this formula, you want the value in the cell in E20:Q130 in the worksheet named in x!B9 for which x!A9 matches A20:A100 (note: there are 111 rows in E20:Q130, but only 81 rows in A20:A100; was one or the other a typo?) and x!K7 matches E26:Q26 (you're looking up a value in a row inside E20:Q130?) in the worksheet named in x!B9.

Are these formulas in just a few worksheets and most of the worksheets which could be named in x!B9 are mostly data or have formulas which don't call INDIRECT? Are most of the formulas calling INDIRECT referring to relative small ranges like A20:Q130? If so, you may be A LOT BETTER OFF consolidating data from all the worksheets which could be named in x!B9 into a single table in a single worksheet with worksheet name and type fields added to the other 17 columns in A20:Q130. Something like (abbreviated)

ws	type	field1	field2	field3
a	_fields_	region	a	b
a	_data_	a	111	112
a	_data_	b	121	122
a	_data_	c	131	132
b	_fields_	division	p	q
b	_data_	E	1	2
b	_data_	C	3	4
b	_data_	W	5	6
c	_fields_	unit	x	y
c	_data_	1.1	a	d
c	_data_	2.7	b	e
c	_data_	4.3	c	f

then index into that table.

I feel that the formula you provide is always a bit weird, try to change it like this see how

=INDEX(INDIRECT("'"&$B9&"'!e20:q130"),MATCH($A9,INDIRECT("'"&$B9&"'!A20:A130"),0),MATCH(K$7,INDIRECT("'"&$B9&"'!e19:q19"),0))