5

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?

pnuts
  • 58,317
  • 11
  • 87
  • 139
user3337882
  • 51
  • 1
  • 1
  • 3
  • 1
    There won't be a function exactly as you ask it which performs better than INDIRECT, because it is the act of seeking out each worksheet & cell reference that causes the formula to be slow. What you are looking for (whether you recognize it this way or not) is for the formula to be pre-calculated once and stored for future reference. To do this, you will need to instead generate the formula dynamically and leave it as a standalone item. I have described how to do that in a fairly manual way in my answer - if you have significant amounts of data like this then you may prefer a VBA solution. – Grade 'Eh' Bacon Oct 29 '15 at 15:31
  • 1
    Please have a look at this: http://stackoverflow.com/a/33306147/3566998 – Excel Hero Oct 29 '15 at 15:38

1 Answers1

3

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.

Grade 'Eh' Bacon
  • 3,773
  • 4
  • 24
  • 46