Index function

[Weimer]

I am trying to reference a text string in place of an array for an index formula. Any suggestions? Basically, I want to simplify some very long index formulas because they use too many characters in one formula for the cell to compute. It's causing excel to lock up on me. Here's what I want to do, but I can't quite get it to work -

Instead of using =index(array,row,column), I want to make cell A1 = a text string representing the file path to the array I need to reference. I'd like to be able to have a formula similar to =index(A1,row, column).

Any ideas would be graetly appreciated.

 

[Juan Pablo González]

That will actually cause more problems than it would solve, because INDIRECT() , the function you could use, is volatile, which means that it gets recalculated EVERY TIME the workbook is calculated, so your calculation times will get worse !

 

[Weimer]

INDIRECT()

Any direction on how to use this function here anyway? Long wait times wouldn't really be an issue. Right now the formula often locks up to the point that excel shuts itself down and I have to recover any unsaved changes. The other part of the issue is the index formula is repeated over a lot of cells. Any changes ( which occur often) to the file path require a tedious replacement process because of the length of the formula and the difficullty excel has in calculating. To be able to make changes in one cell and refresh would also help a lot.

 

[Aladin Akyurek]

Re: INDIRECT()

Any direction on how to use this function here anyway? Long wait times wouldn't really be an issue. Right now the formula often locks up to the point that excel shuts itself down and I have to recover any unsaved changes. The other part of the issue is the index formula is repeated over a lot of cells. Any changes ( which occur often) to the file path require a tedious replacement process because of the length of the formula and the difficullty excel has in calculating. To be able to make changes in one cell and refresh would also help a lot.

If you insist...

=INDEX(INDIRECT(A1),RowIdx,ColIdx)