Alternative to INDIRECT

Question

I'm using the INDIRECT function to fetch data from another table sheet. The direct way would be "sheetname!A5". I need it dynamically, so the sheetname is also part of the table, using indirect "indirect("'"&G$1&"'!A5"), whereas G1 contains the sheetname works fine.

INDIRECT is horribly slow, which outperform my project because I need a lot of fetches.

Is there a less powerful / more performant function to fetch data from variable sheets?

Answer 1

I've run into similar situations - depending on how frequently your 'results' worksheet changes, it may be best to create a formula dynamically that initially 'writes' hardcoded formuls. For example: ="='"&G$1&"'!A5".

Then copy that formula as you wish; it will create text strings that represent what you want the formulas to look like. Then copy & paste as values - you will now have the desired formula entered into each cell. By default, Excel will still display each cell as a text string, even though the format type would be general (assuming it was beforehand). From there, different solutions are possible - if you have few enough cells it may be enough to just start at the top pressing F2 then Enter, repeatedly, to get Excel to calculate each cell.