Previously I posted up two threads regarding a formula that could pull data by referencing a sheet name in a cell - I was recommended the following formulas:

=IF(ISBLANK($B6),"",(INDIRECT("'"&$B6&"'!"&E$2)))
=SUMPRODUCT(N(INDIRECT("'"&ROW(INDIRECT("1:420"))&"'!"&CELL("address",P23))))
Both of these formuals appear on different summary sheets and work but are very time consuming

Research I've found out that INDIRECT is 'volative' as it continually calculates every cell and hence the slow down in speed.

Can anyone suggest any other formulas?