Jerry,
Sorry for my slow response. And thanks for the suggestion.
Update: I have done some benchmarks and the issue is definitely the INDIRECT function is the culprit. I was able to get a 40% speed-up by removing the CONCATENATE commands from within the INDIRECT and building the string for the INDIRECT in a separate cell. If I remove the INDIRECT calls, the spreadsheet is very fast.
Background: The problem I am trying to solve is that I have a piece of lab equipment that collects data--lots of data--and exports it in CSV format. The imported file is organized in a two dimensional fashion since it is reading a plate that is organized in the same fashion. So I add to the raw imported file a mapping for what each data point represents. This "purpose" is also arranged in a two dimensional fashion and varies from experiement to experiment. The crux of the problem is that I want to export the data to JMP for statistical analysis but the data has to be converted from this two diminsional array to a more conventional format where each data point is represented by a row. The row contains the purpose and physical data related to the experiment and the single data point from the grid. So I end up with a file with lots of rows at the end. So with the INDIRECT statement I can tell Numbers to lookup the data point with the criteria of this experiement with this purpose and it fills in all the rows in a format that can be exported to JMP. You can see the flexibility.
Your Suggestion: So the cool part of your suggestion is that I can use INDIRECT to find the first data point in my database and then perhaps all of the subsequent rows can be filled in with INDEX and OFFSET. I will give this a try and post here. Right now I am in a 30min wait cycle for the INDIRECT calls to execute on a dataset represented by 240 rows, just because I opened it.
Plan B: The other solution I was considering since the overhead of Numbers grows expoentially with the size of the dataset, is to Cut and "Paste Values" after the spreadsheet computes the results for a single experiement. Thus I can manually build-up my export table, by doing a ton of small cut and paste operations into a much smaller table that has the INDIRECT functions calls.
Bug: I consider this to be a bug in Numbers in that the overhead of doing INDIRECT calls grows exponentially less efficient as the number of calls to INDIRECT increases. The interesting thing is that the input data set was not growing in this benchmark, but only the calls to INDIRECT.
